For next loop and copy and pasting VBA

[carlos_ray86]

I'm trying to do a copy and paste. However, I need the paste to paste
in a different row so they don't overlay. Ihave been trying to do for
loops so the the loop would cause the row number to change (i.e. A3 A4
A5). But no success. Please help.

 

[Guest]

Post what you have so far and I'm sure someone can assist.

 

[meh2030]

I'm trying to do a copy and paste. However, I need the paste to paste
in a different row so they don't overlay. Ihave been trying to do for
loops so the the loop would cause the row number to change (i.e. A3 A4
A5). But no success. Please help.

Do you have some sample code or a more detailed description?

Not knowing exactly what you are doing, maybe the following sample
code will help. It takes whatever is in A1, copies the content, and
then pastes it in the subsequent rows according to the number in the
For Loop.

Sub copyPaste()

Dim a As Integer

For a = 1 To 10
Range("a1").Copy Destination:=Range("a" & a + 1)
Next

End Sub


Matt

 

[Guest]

Carlos,

It would have been better to have posted what you have tried. However, a
couple of ways to loop throuhj cells.

Sub atomicparticles()
Dim myRange As Range
Set myRange = Range("A3:A20")
For Each c In myRange
c.Select
'do something
Next
End Sub


Sub stantial()
For x = 1 To 20
Cells(x, 1).Select 'A1
'DO SOMETHING
Next
End Sub

Mike

 

[Guest]

Private Sub copyPaste()
Const columnA = "A"
Dim actWS As Worksheet, i As Long
Dim lastrowInColumnA As Long

Set actWS = ActiveSheet

lastrowInColumnA = actWS.Cells(Rows.Count, columnA).End(xlUp).Row

For i = 3 To lastrowInColumnA
'Dosomthing

Next i
End Sub

 

[carlos_ray86]

this is the base of my script so far

Sub PasteSpecial()
Dim DeleteRows As Range
Range("A1:A3").Copy
Range("C4").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Set DeleteRows = Range("A1:A8") ' this deletes the row that I just
copied. i.e. cut
DeleteRows.Delete Shift:=xlShiftUp
End Sub


This works great but it will overlap, paste over paste, becuase I
have
the second range set to C4.


I want some how for the C4 to change after every copy so it would be
like this...
Range("A1:A3").Copy
Range("C4").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Range("A1:A3").Copy
Range("C5").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True


Range("A1:A3").Copy
Range("C6").Select ' This is the problem
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True