Macro pasting 1 row down each time

[tinmar]

Currently I have a macro which grabs a line of data from sheet 1, and pastes it say to sheet 3 on cell A1. I am trying to make the macro on the next run, to paste it in 1 row down and not affect the data in cell A1, I know you can do it, I just can't work it out... please help.

 

[PHV]

what is your actual code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tinmar]

HEre it is in simple form

Range("B23").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet3").Select
Rows("1:1").Select { each time i run the macro, I want it to paste it in 1 cell down from the previous time it was run}
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub

 

[PHV]

You may try something like this:
[!]Static r As Long
r = r + 1[/!]
Range("B23").Select
...
Rows([!]r[/!]).Select
...
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[abaldwin]

Do you want to increment this pasting even after closing the sheet and opening the same wb tomorrow?

If so then you need to modify you code, just before the pastespecial line and insert code that will loop through the sheet 3 and find the first empty line. As it loops increment your "r" variable that PHV has you insert. As soon as you find the first empty row, then do the paste.

Andy Baldwin

"Testing is the most overlooked programming language on the books!