On Thu, 1 Dec 2011 18:34:40 -0800 (PST), Mark wrote:

On Nov 28, 4:14*pm, Ron Rosenfeld wrote:
On Sun, 27 Nov 2011 22:20:11 -0800 (PST), Mark wrote:
So we have
declared that wb is a Workbook but we haven't defined which workbooks
it should be searching to get the information.

The workbook that wb gets assigned to is via the :

For Each wb In Workbooks

statement.

Then we except the wb that you have the results going into, and we should also be skipping any wb's that don't have an "Admin" worksheet. *So I don't understand your error you mentioned in your next post, unless possibly that workbook had an Admin worksheet, but had its data on the Administration worksheet.

Thanks for that explanation, makes sense now.

I can see the code there that should be skipping over wb's without an
"Admin" sheet but it definitely errors when it has a different name
(also no "Admin" sheet on the one with the incorrect name).

In a perfect World I'd have the code skipping over those books but I'm
ok to do the better housekeeping to keep all wb's consistent.

Thanks Again

Puzzling over the code, I think I have found the problem. Once ws gets set to an admin worksheet, it remains set there, and doesn't go back to "nothing" when we check the next wb. Hence the error.

Try adding

Set ws = Nothing

in that section as shown below, and that should take care of the error.

--------------------------

For Each wb In Workbooks
If Not wb.Name = "Book2" Then 'or whatever book holds the results
Set ws = Nothing
On Error Resume Next
Set ws = wb.Worksheets("Admin")
On Error GoTo 0
If Not ws Is Nothing Then
Set rSrc = wb.Worksheets("Admin").UsedRange

-------------------------------------------------