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

I don't have an answer to best, but insertion would be to set a variable to
the result of whatever method. Then modify your code to do the following at
least for the copy/select. Assume the variable is called NumLastRow
Where it says Range("A3:AF44").Select use
Range("A3:AF" & NumLastRow).Select

The designation of NumLastRow actually equalling the last row, must be
somewhere before this line.

To improve on your current code. I don't see why the SmallScroll is needed.
(You may have scrolled the window when recording the macro. So you could
delete that line.

Second, because the record macro option builds the macro as you take action,
you do not need to have the Range.Select followed by Selection Copy. You
could just have Range (as inserted above).Copy and delete the selection.copy
line. Similar actions could be "combined" in the lines following it.

Taking a quick look, I think both do the same thing, and it is a matter of
you being able to understand them when you're done. The second function
appears to resize the height of the rows, however I can not currently
investigate what Resize does.

Beware that both functions operate off of column A, and start at A1. If you
do not have data in every row of column A for which you want to determine
your last row, then this will not do what you want. I think that the
functions actually require contiguous data, but I'm not sure, again I do not
have access to the VBA help files at the moment. :\



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

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