copy data input to archive

[access user]

Hi

I would like to copy data input rows from sheet InputData to another sheet
called Archive. The data is from rows 2 onwards (row 1 is header info). On
the archive sheet the data should get appended to the first blank row. Can
someone help me with a macro for this?

thanks
James

 

[Joel]

With Sheets("Archive")
ArchLastRow = .Range("A" & Rows.Count).End(xlUp).Row
End With
With Sheets("InputData")
InLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("2:" & InLastRow).Copy _
Destination:=Sheets("Sheet2").Rows(ArchLastRow + 1)
End With

 

[Joel]

I had sheet2 instead of Archive in one place in the code

With Sheets("Archive")
ArchLastRow = .Range("A" & Rows.Count).End(xlUp).Row
End With
With Sheets("InputData")
InLastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows("2:" & InLastRow).Copy _
Destination:=Sheets("Archive").Rows(ArchLastRow + 1)
End With

 

[dan dungan]

Hi James,

Have a look at Ron de Bruin's website:

http://www.rondebruin.nl/tips.htm.

There is a Copy/Paste/Merge examples section.

hth

dan

 

[access user]

Joel

Thanks for that. Additionally, I forgot to mention, I wanted to erase the
data (that's copied to the archive sheet) from the input sheet. So that after
each transfer of data to the archive sheet, the range ont the input sheet
where the data was copied from is cleared. Is that quite easy to do
programmatically?

tia
James

 

[access user]

What is "ArchLastRow" out of interest? How does excel know what it is?