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??