Some suggestions...
Declare the vArr and sh variables.

Change the sh and destrange references as follows...
For i = LBound(vArr) To UBound(vArr)
Set sh = myBook.Worksheets(vArr(i))
Set sourceRange = sh.UsedRange
SourceRcount = sourceRange.Rows.Count
Set destrange = baseBook.Worksheets(sh.Name).Range("B" & rnum)

Change all other references to the basebook sheet as above.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Ray"
wrote in message
Hi -
I need to 'consolidate' data into one Master Workbook -- my current
code is below. The data currently sits in approx 30 workbooks and is
spread across a number of worksheets within each WB. All WB are set
up exactly the same way -- same sheet names, etc. The Master WB also
has the same Sheet names (to keep things simple).

My code worked fine when just hitting one worksheet within each WB.
However, when I modified the code to pull from all of the sheets, it
didn't work at all! My modification was to activate the vArr code and
change all references to the single worksheet to use the 'ws'
reference. When I run the code now, the first WB opens and then the
code stops.

What's causing this and how do I fix it?

A couple of other small things aren't working -- there's code to
isolate the store number from the name of each target WB and insert it
into Column A. It should put this store number next to EACH ROW that
is transferred, but it currently just puts it in the first row.

When all data has been pulled from the target WBs, I want all blank
rows to be deleted from each data tab in the Master WB. A blank row
is any row where cells Ax & Bx (where x is row #) are blank.

-snip-