right code, wrong range

[Ray]

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

 

[Jim Rech]

sh.Cells(2, 50).End(xlUp))

2 rows and 50 cols? You probably meant to switch these.


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

 

[Ray]

2 rows and 50 cols? You probably meant to switch these.

--

| 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