copy and paste macro

[Robert]

I need help creating a macro to copy cells C3:C22 and then PasteSpecial,
ValuesOnly to cells O12:O32. Then the next time I run the macro, I need it
to paste the C3:C22 values into the next available blank column, P1232. It
has to be able to do that for a total of 12 times, each time using the naxt
available blank column. It then has to automatically erase it all, O12:Z32.
Then it would start over with the O column again.

I would appreciate it if somebody could show me the complete macro to
accomplish these steps. Thank you.

Robert

 

[Simon Lloyd]

This should do what you need


VBA Code:
--------------------


Sub CopyPastex12(
Dim i As Lon
i = 1
For i = 15 To 26 Step
Range("C3:C22").Cop
If ActiveSheet.Cells(12, i) = "" The
ActiveSheet.Cells(12, i).PasteSpecial Paste:=xlPasteValue
Els
End I
Next
Application.CutCopyMode = Fals
End Su

--------------------






I need help creating a macro to copy cells C3:C22 and the
PasteSpecial
ValuesOnly to cells O12:O32. Then the next time I run the macro, I nee
i
to paste the C3:C22 values into the next available blank column
P1232. I
has to be able to do that for a total of 12 times, each time using th
nax
available blank column. It then has to automatically erase it all
O12:Z32
Then it would start over with the O column again

I would appreciate it if somebody could show me the complete macro t
accomplish these steps. Thank you

Robert


--
Simon Lloyd

Regards
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)

 

[Robert]

Simon, thank you for the information but I'm afraid I'm not smart enough to
understand the answer. I am running Excel 2002 and I'm wondering if your
answer applies to a later version. I should have sent you what I already
have and asked you to fix that. Here is what I have so far. It of course
just copies everything into the first column and never advances to the next
empty one.

Sub copy_paste()
'
' copy_paste Macro

'
' Keyboard Shortcut: Ctrl+c
'
Range("C3:C23").Select
Selection.Copy
Range("O12:O32").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C3").Select
Application.CutCopyMode = False
End Sub

Thank you again for your help. I've been struggling with this for days.

Robert

This should do what you need:

 

[Simon Lloyd]

Did you try replacing your code for mine? it should have worked as
needed, just replace yours with the code below and it will populate 12
coulms with the data as requested:


VBA Code:
--------------------


Sub CopyPastex12()
Dim i As Long
Activesheet.range("O12:Z32").clearcontents
i = 15
For i = 15 To 26 Step 1
Range("C3:C22").Copy
If ActiveSheet.Cells(12, i) = "" Then
ActiveSheet.Cells(12, i).PasteSpecial Paste:=xlPasteValues
Else
End If
Next i
Application.CutCopyMode = False
End Sub
--------------------




If it doesn't work or you don't know how to use it please explain what
you get when it doesn't work or how you are trying to use it.


Simon, thank you for the information but I'm afraid I'm not smart
enough to
understand the answer. I am running Excel 2002 and I'm wondering if
your
answer applies to a later version. I should have sent you what I
already
have and asked you to fix that. Here is what I have so far. It of
course
just copies everything into the first column and never advances to the
next
empty one.

Sub copy_paste()
'
' copy_paste Macro

'
' Keyboard Shortcut: Ctrl+c
'
Range("C3:C23").Select
Selection.Copy
Range("O12:O32").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C3").Select
Application.CutCopyMode = False
End Sub

Thank you again for your help. I've been struggling with this for days.

Robert

This should do what you need:

VBA Code:

Sub CopyPastex12()
Dim i As Long
i = 15
For i = 15 To 26 Step 1
Range("C3:C22").Copy
If ActiveSheet.Cells(12, i) = "" Then
ActiveSheet.Cells(12, i).PasteSpecial Paste:=xlPasteValues
Else
End If
Next i
Application.CutCopyMode = False
End Sub
I need help creating a macro to copy cells C3:C22 and then
PasteSpecial,
ValuesOnly to cells O12:O32. Then the next time I run the macro, I need
it
to paste the C3:C22 values into the next available blank column,
P1232. It
has to be able to do that for a total of 12 times, each time using the
naxt
available blank column. It then has to automatically erase it all,
O12:Z32.
Then it would start over with the O column again.

I would appreciate it if somebody could show me the complete macro to
accomplish these steps. Thank you.

Robert


--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: 'copy and paste macro - The Code Cage Forums' (http://www.thecodecage.com/forumz/showthread.php?t=194378)

'Microsoft Office Help - Microsoft Office Discussion - Excel VBA

Programming - Access Programming' (http://www.thecodecage.com/forumz)

.

--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)

 

[Robert]

Thanks again Simon for all your time. I do appreciate it.

When I run the macro one time, it fills all the columns in and then stops.
I can run it over again and it does the same thing.

I pasted your code into an empty module and assigned a shortcut key to it
and then run it with that.

Am I doing something wrong?

Robert

Did you try replacing your code for mine? it should have worked as
needed, just replace yours with the code below and it will populate 12
coulms with the data as requested:

VBA Code: