Ohh, I also assumed that only the last row would change. If the first row
will also change, you can make a similar program change to "locate" the first
row, but you will have to be sure that you can actually identify the first
row either from some known information or by user interaction.


"dannykuk" wrote:

I have recorded the following Macro
Windows("Copy of Headcount Review 2006.xls").Activate
ActiveWindow.SmallScroll Down:=-21
Range("A3:AF44").Select
Selection.Copy
Windows("Core Data Example.xls").Activate
Sheets("UK Downstream").Select
Range("A3").Select
Selection.Insert Shift:=xlDown
This is to import data which will be arriving Friday from 20+ business units
into a single core data sheet with tabs for each unit. The problem I have is
the Range("A3:AF44").Select line the reason being that although the columns
will be A:AF will stay constant the rows will vary. I have found the
following pieces of code to select a variable range
dim LastRow as long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("a1:x" & lastrow).copy _
destination:=....
end with
Or
Set rng = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp)).Resize(,
21)
rng.Name = "range_name"
Would anyone be able to tell me which is best and how i would insert it into
the code above??