Copy selection based on relative reference
 

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

Copy selection based on relative reference
 

Try this:
Replace
ActiveCell.Range("A1:B32").Select
Selection.Copy

with
Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Copy

-Reuel


"goofy11" wrote:

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

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

Copy selection based on relative reference
 

Thanks, that did the trick!

"Reuel" wrote:

Try this:
Replace
ActiveCell.Range("A1:B32").Select
Selection.Copy

with
Selection.Resize(Selection.Rows.Count - 1, Selection.Columns.Count).Copy

-Reuel


"goofy11" wrote:

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