In VBA, bump to next row each iteration

  • Thread starter Thread starter Chuck M
  • Start date Start date
C

Chuck M

I am a VBA novice. I've created VBA code by recording Excel macros. I have a
macro that copies cells from one worksheet and pastes them into another in
the same workbook. I want to run the macro repeatedly and have each
successive row in the target worksheet start one row below the previous one.
I can pick up the row number from the source worksheet if that would help.
But I don't know how to convert it into a row number in the target worksheet
 
Code supplied below. It is the Range("D2").Select line that i'd like to
change into soemthing dynamic. I.E. The next time this macro executes, I'd
like the active cell (target ot the PasteSpecial) to be D3, then D4, etc.

Sub Process4()
Range("A13:A16").Select
Selection.Copy
Sheets("Work Area").Select
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
Range("A5").Select
End Sub
 
chuck:

try something like this. change the name of the sheet you're copying from (i
used sheet1 in the code). i assumed there was a heading in D1.

Sub Process4()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastrow As Long

Set ws = Worksheets("Sheet1")
Set ws2 = Worksheets("Work Area")
lastrow = ws2.Cells(Rows.Count, "D").End(xlUp).Offset(1).Row
ws.Range("A13:A16").Copy

ws2.Range("D" & lastrow).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Range("A5").Select
End Sub
 
Back
Top