Solved: macro to copy to next empty column

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

costagin

Thread Starter
Joined
Apr 15, 2010
Messages
8
My operating system is XP Professional
I'm using Excel 2002

I'm trying to create a macro to copy a column of data from cells C3:C23 and paste it into a range of columns starting with O13:O33 and ending with Z13:Z33. Each time I run the macro I want it to advance to the next empty column and PasteSpecial, ValuesOnly in that column. After all 12 columns have data in them, the next time the macro runs it has to delete all twelve columns and start over at the first column O13:O33.

The following is what I have done but it only pastes into the first column and never advances to the next one.

Range("C3:C23").Select
Selection.Copy
Range("O13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

Thanks for your help.

costagin
 

Ent

Josiah
Trusted Advisor
Joined
Apr 11, 2009
Messages
5,467
I think that this is what you need.

Code:
Dim ColCount As Integer
Dim RowClear As Boolean
ColCount = 0
RowClear = False

Do
ColCount = ColCount + 1
RowClear = True

For r = 13 To 33
If Cells(r, ColCount + 14).Value <> 0 Then RowClear = False
Next r

Cells(1, 1).Value = RowClear
Cells(1, 2).Value = ColCount
Loop Until ColCount = 13 Or RowClear = True

If ColCount = 13 Then
ColCount = 1
Range("O13", "Z33").ClearContents
End If

For r = 3 To 23
Cells(r + 10, 14 + ColCount).Value = Cells(r, 3).Value
Next r
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top