Do While Loop to Import Worksheets from Various Workbooks

Hi, I am fairly new to macros and have a problem. I want to import specific worksheets from various workbooks. The file destination can be found on col B and the worksheet name can be found on col C. So far I have come up with the formula below but it doesn't seem to work. I have attached the Main(which is the destination workbook) and 2 Test workbooks where the worksheets have to be attracted from. I would like to thank anyone who is helping me in advance

Sub GrabWBs()

Dim x As Integer


x = 2

Do While Cells(x, 1) <> ""

Workbooks.Open Filename:=Cells(x, 2)
Worksheets(Cells(x, 3)).Select
Worksheets(Cells(x, 3)).Copy After:=Workbooks("Main.xlsm").Sheets("Control")
Workbooks(Cells(x, 2)).Select
ActiveWorkbooks.Close

x = x + 1

Loop



End Sub

kchm_2000,

Right away I noticed some problems in the setup. Your code looks if Cell(x,1) is empty. But you don't have any data in column A in the Main workbook, so the first cell it looks at is empty and then it exits the macro. Also, the sheet you wanted from Test2.xlsx didn't exist. It looks like Test.xlsx was just copy/pasted wholesale, so they both have a worksheet named "1" and Test2 did not have a worksheet named "2" which was specified in the Main workbook.

I renamed the "1" worksheet in Test2 to "2" so that it matched what the Main workbook was searching for, and then I updated your code:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Wow thanks, I think i got a little careless. But is there a way to make the program more robust such that if it detects that there is already a worksheet (1) in the destination spreadsheet, it would delete it and replace it with the one from the copied one? So far the code is working wonderfully thanks a bunch