Row.Count is a constant to get the last row of the worksheet like 65536 for
Excel 2003. The code Below goes to AD65536 and the end method moves up the
worksheet until a Non-Empty cell is found.

LastRow = sheets("Data").Range("AD" & Rows.count).end(xlup).Row
set CopyRange = sheets("Data").Range("AD2:AD" & LastRow)
CopyRange.Copy Destination:=Sheets("Sheet1").Range("A1")


"AmyHomewood" wrote:

I have a workbook with 2 sheets, the purpose of which is to take data from a
seperate file and configure it to enable it to be exported.

Sheet one is "Data" which the user copies data into from the other file, the
length (number of rows) changes each time data is copied in.
Sheet two "SDB" is basically a mapping sheet, the first row is column
headings and the second row has the data taken from "Data" e.g. =Data!AD2.

I want to create a macro to select row 2 and copy it down as many times as
there are rows in the "Data" sheet. I can count how many rows there are but I
can't figure out how to use this variable in the macro.

Thanks in advance,

Amy Homewood