In VBA, bump to next row each iteration
 

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

In VBA, bump to next row each iteration
 

Show your code.

Tim

"Chuck M" wrote in message
...
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

In VBA, bump to next row each iteration
 

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

"Tim Williams" wrote:

Show your code.

Tim

"Chuck M" wrote in message
...
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

In VBA, bump to next row each iteration
 

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

--


Gary


"Chuck M" wrote in message
...
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

"Tim Williams" wrote:

Show your code.

Tim

"Chuck M" wrote in message
...
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

In VBA, bump to next row each iteration
 

Gary,
Thank you so much! It definitely looks like this is going to work for me.

"Gary Keramidas" wrote:

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

--


Gary


"Chuck M" wrote in message
...
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

"Tim Williams" wrote:

Show your code.

Tim

"Chuck M" wrote in message
...
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