Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a relative cell reference that will copy the format? | Excel Discussion (Misc queries) | |||
How do I copy a combobox to many cells with relative reference? | Excel Discussion (Misc queries) | |||
Copy data from pivot table using relative reference | Excel Discussion (Misc queries) | |||
Copy a relative reference formula from one sheet to another. | Excel Discussion (Misc queries) | |||
Excel - Copy range of cells based on Combobox Selection | Excel Programming |