VBA script for moving tabs from one workbook to another

shayb

Board Regular
Joined
Apr 21, 2004
Messages
81
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

shayb

Board Regular
Joined
Apr 21, 2004
Messages
81
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

Board Regular
Joined
Apr 21, 2004
Messages
81
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,069
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
Set wb = Workbooks.Open(Filename:="MyWB.xls")

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

wb.Close
 

shayb

Board Regular
Joined
Apr 21, 2004
Messages
81

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,069
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 21, 2004
Messages
81

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,069
Office Version
  1. 365
Platform
  1. Windows
Just add this.
Code:
Dim wb As Workbook
 

shayb

Board Regular
Joined
Apr 21, 2004
Messages
81
It says method 'Copy' of object "Sheets" failed.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,475
Members
412,667
Latest member
rpbenz
Top