Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
right code, wrong range
Hello -
I'm using the following code to 'consolidate' info from multiple sheets into a Master sheet. It works great, except that I'm only getting the first two rows from each sheet. I've tried to modify the code, but keep getting tripped up by xlLeft, xlUp, etc. How should the code be modified to pull in the full used range on each sheet? Sub AABB() Dim i As Long Dim sh As Worksheet Dim rng As Range vArr = Array(......) For i = LBound(vArr) To UBound(vArr) Set sh = Worksheets(vArr(i)) Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 50).End(xlUp)) rng.Copy Destination:=Worksheets("Master") _ .Cells(Rows.Count, 1).End(xlUp)(2) Next End Sub TIA, Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
right code, wrong range
sh.Cells(2, 50).End(xlUp))
2 rows and 50 cols? You probably meant to switch these. -- Jim "Ray" wrote in message oups.com... | Hello - | | I'm using the following code to 'consolidate' info from multiple | sheets into a Master sheet. It works great, except that I'm only | getting the first two rows from each sheet. I've tried to modify the | code, but keep getting tripped up by xlLeft, xlUp, etc. | | How should the code be modified to pull in the full used range on each | sheet? | | Sub AABB() | Dim i As Long | Dim sh As Worksheet | Dim rng As Range | vArr = Array(......) | For i = LBound(vArr) To UBound(vArr) | Set sh = Worksheets(vArr(i)) | Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 50).End(xlUp)) | rng.Copy Destination:=Worksheets("Master") _ | .Cells(Rows.Count, 1).End(xlUp)(2) | Next | End Sub | | TIA, Ray | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
right code, wrong range
On Mar 22, 9:58 am, "Jim Rech" wrote:
sh.Cells(2, 50).End(xlUp)) 2 rows and 50 cols? You probably meant to switch these. -- Jim"Ray" wrote in message oups.com... | Hello - | | I'm using the following code to 'consolidate' info from multiple | sheets into a Master sheet. It works great, except that I'm only | getting the first two rows from each sheet. I've tried to modify the | code, but keep getting tripped up by xlLeft, xlUp, etc. | | How should the code be modified to pull in the full used range on each | sheet? | | Sub AABB() | Dim i As Long | Dim sh As Worksheet | Dim rng As Range | vArr = Array(......) | For i = LBound(vArr) To UBound(vArr) | Set sh = Worksheets(vArr(i)) | Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 50).End(xlUp)) | rng.Copy Destination:=Worksheets("Master") _ | .Cells(Rows.Count, 1).End(xlUp)(2) | Next | End Sub | | TIA, Ray | Hi Jim - Thanks for the response ... I'd actually 'recalled' my post, b/c I figured out the answer myself! Yahoo! I replaced Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 50).End(xlUp)) With Set rng = sh.UsedRange so simple, yet so much frustration.... ;) //ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there something wrong with my code? | Excel Programming | |||
What is wrong with this code to name a worksheet range | Excel Programming | |||
What's wrong with this code? | Excel Programming | |||
What's wrong with this code, please? | Excel Programming | |||
What's wrong w/my code? | Excel Programming |