VBA script for moving tabs from one workbook to another

[shayb]

I have an Excel workbook that I have several worksheets in. I have a macro that I am going to run from this workbook. What I want this macro to do is open another workbook with several worksheets in it and move all of the worksheets from it into this file. That is it. I am new to vb script and I am totally lost. Please help.

Thanks.

 

[shayb]

Never mind...found the solution through another message

Never mind...found the solution by searching through other messages. Should have tried that first. Sorry.

 

[shayb]

Still Need Help!

Still Need Help. Sorry but the message I followed was for something else. Please read my original post and help. Thanks.

 

[Norie]

I'm afraid your original post isn't quite clear.

Which workbook are you copying from and into which workbook are you copying?

Do you want to copy the worksheets in the newly opened workbook into the workbook with the code?

Set wb = Workbooks.Open(Filename:="MyWB.xls")

wb.Worksheets.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

wb.Close

 

[shayb]

I am sorry. The original file that I am running the macro from is the file I am copying the worksheets into.

The macro needs to go and opens another file and move all of the worksheets from that file into this file. When I recorded the macro, I clicked on the first tab and then held my shift key down and clicked on the last tab. I then did the move process in Excel. When I went to run the macro, it crashes. This is where I am lost.

There must be a simple vb script that says select all worksheets in a workbook and move them to another file. Let me know. Thanks.

 

[Norie]

Did you try the code I posted?

It opens a workbook called MyWB and copies all the worksheets into the workbook where the code is located.

By the way if you truly want to move all the worksheets then of course you will get errors - a workbook must have at least 1 sheet.

 

[shayb]

I did but is gave me some kind of error message say wb not defined. Do I have to have a DIM statement somewhere?

 

[Norie]

Just add this.

Dim wb As Workbook

 

[shayb]

It says method 'Copy' of object "Sheets" failed.

Private Sub move_reports()

  Dim wb As Workbook
  
  Set wb = Workbooks.Open(Filename:="\\Wfldfp02\GSMS_CorpAccts\Business Reviews\Data\Corp Acct Templates NEW 12-05.xls")

wb.Worksheets.Copy Before:=ThisWorkbook.Worksheets(1)
wb.Close