Copy selection based on relative reference
With Workbooks("Order Tracking.xls").ActiveSheet _
.Range("A1").CurrentRegion
.Resize(.Rows.count-1,2).Copy
End With
--
Regards,
Tom Ogilvy
"goofy11" wrote in message
...
I've recorded a macro, but am having trouble on one part. I know very
little
about VBA, but am sure this is a simple fix. Each week my data set grows
by
one row. This week the data set consists of 33 rows, however, I only want
to
copy 32 rows. To say it another way, I always want to copy all rows
EXCEPT
the last one. When recording the macro, I turned relative reference on.
I
then used CTRL+SHIFT+DOWN, then SHIFT+RIGHT (because I want 2 columns),
then
SHIFT+UP (to un-select the last row). This highlights my range, and then
I
copy. When running my macro this week, I noticed it only grabbed the same
32
rows from last week. Sure enough, the VBA code specified A1:B32. How can
I
alter the code to grow with the data? Below is the recorded VBA for this
part.
Windows("Order Tracking.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 1).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:B32").Select
Selection.Copy
Any help would be appreciated.
Jeff
|