I'm writing a macro for Excel that will copy large ranges of data and paste
into a new worksheet preserving the historical data. The number of rows to
copy changes dynamically on almost a daily basis.

Is there a function that can be written into a macro to change the range
selectiion each time new data is available. Each row is a transaction record.

See Ron de Bruin's site

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

Browse through the section on Copy/Paste/Merge examples


Gord Dibben MS Excel MVP

On Fri, 28 Sep 2007 10:07:01 -0700, Bill Lowry <Bill
wrote:

I'm writing a macro for Excel that will copy large ranges of data and paste
into a new worksheet preserving the historical data. The number of rows to
copy changes dynamically on almost a daily basis.

Is there a function that can be written into a macro to change the range
selectiion each time new data is available. Each row is a transaction record.

Dim r as Range, r1 as Range
with worksheets("Destination")
set r = .cells(rows.count,1).End(xlup).row
End with
with worksheets("Data")
set r1 = .Range("A1").CurrentRegion
r1.offset(1,0).copy r(2)
End With

--
Regards,
Tom Ogilvy



"Bill Lowry" wrote:

I'm writing a macro for Excel that will copy large ranges of data and paste
into a new worksheet preserving the historical data. The number of rows to
copy changes dynamically on almost a daily basis.

Is there a function that can be written into a macro to change the range
selectiion each time new data is available. Each row is a transaction record.