Merging Excel workbooks

[jjefferson]

I've found and am using some techniques from this forum to retrieve information from multiple workbooks and copy it into a "master" workbook. This is working fine when the "source" workbooks only contain one worksheet.

Now, I have to open from 1 to 12 workbooks, each containing from 1 to 22 worksheets, and pull the data in to one master workbook (which I will manipulate later).

I am having problems with the following code block:

   For i = 1 To intThisMonth
     strSheetName = strPeriod(i)
     MsgBox ("opening workbook for " & strSheetName)
     Workbooks.Open "G:\DIR1\BOOK-" & strSheetName & ".xls"
     Set wbTmp = ActiveWorkbook
     intWorkSheetCount = ActiveWorkbook.Sheets.Count
     MsgBox ("There are " & intWorkSheetCount & "worksheets in this workbook")
     For j = 1 To intWorkSheetCount
        wbTmp.Sheets(j).Select
        wbTmp.Sheets(j).Copy After:=wbMaster.Sheets(wbMaster.Sheets.Count)
     Next j
     wbTmp.Close SaveChanges:=False
     MsgBox ("Closed the workbook")
   Next i

What happens is this:

1. When I run the macro using the shortcut key assigned, I see the first message box about opening the correct workbook, and then the first workbook in the series is opened. However, everything just stalls at that point. Nothing is frozen, but the macro does not execute any further.

2. When I run the macro from within Excel's VB editor, the first workbook is opened and I see the message showing me the correct number of sheets in that workbook. When continuing, the macro errors out at the line "wbTmp.Sheets(j).Select", giving me error 1004 - Select method of Worksheet class failed.

I'm stumped, and hope someone can help get me over this one hurdle.

Thanks!
Jim

 

[jjefferson]

Update from original poster:

On further testing, it appears to be a problem with the Select method on the second sheet in the source workbook. I suspect that I need to (somehow) cancel the selection of the previous worksheet before selecting the next; perhaps a selection cannot span worksheets.

If this is the case, how does one (in VBA) unselect or otherwise cancel the previous selection, so a select of another sheet in the active workbook will not fail?

Thanks again,
Jim

 

[jjefferson]

From original poster:

Okay, I got it to work. What I had to do was add the line:

Workbooks(wbTmp.Name).Activate

after the .Copy method for the current sheet. I guess the macro (or me) was losing "focus" somewhere, and I had to get it back on the source workbook to select and copy the next sheet.

I don't know if this is what was missing, or if it was the appropriate action, but it works and that's saying a lot for me. If anyone has a more elegant solution to pulling in the contents of a number of workbooks into a single workbook, I'm all ears!

Thanks,
Jim