Combine all Open Workbooks Into One Workbook

S

sdh31873

New Member
Joined
Jan 29, 2007
Messages
36
I have read all post regarding this and still can't find a simple solution. I have 9 workbooks open and each workbook has 1 worksheet with various sheet names. I am trying to combine them all into 1 new workbook with the sheet names remaining the same.

Basically, I want to do a "Move Sheet" to New Workbook and then close the old workbook but I want to automate it to do all 9 files. Hope this makes sense.

In a nutshell, I want to merge 9 open workbooks into 1. Thanks.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
something along the lines of

Code: 
Sub Macro1()
Dim wb As Workbook
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
For Each wb In Application.Workbooks
    If wb.Name = wbThis.Name Then GoTo 10
    wb.ActiveSheet.Copy After:=wbThis.Sheets(Sheets.Count)
    wb.Save
    wb.Close
10
Next wb
End Sub
 
Upvote 0
I get a "Copy Method of Worksheet Class Failed". Not sure. I created a new blank workbook and ran the macro. I have the other workbooks open as well. Thanks.
 
Upvote 0
Hi Dave,

How come you set wbThis = ThisWorkbook? Is there a reason to not just use ThisWorkbook repeatedly?

Thanks,
Tai
 
Upvote 0
There is no reason not to use ThisWorkbook repeatedly I set the wbThis because it was like that in the code I amended to provide assistance to the OP ;)

When you created the new workbook did you create it by going to File>>New or did you open a new instance of Excel?
 
Upvote 0
I did File - New. I even recorded the macro as previously mentioned and still get the same error message. "Copy Method of Worksheet Class Failed". Surely there is an easier way of consolidating several open workbooks into one. I just can't seem to get it. Thanks.
 
Upvote 0
Google seems to suggest your system could be low on memory, could this be an issue? Have you alot of apps running at the moment
 
Upvote 0
I don't have anything running. I just closed everything down and it is still the same. I am stuck. Thanks for all your help.
 
Upvote 0
I just noticed one thing but I can't be positive that this is it. I am looking at the immediate window in VBA and it shows when it executes the macro, it is trying to copy the sheets from Personal.xls and it does this 3 times. I have the macro saved in Personal.xls because I want everyone to access it when needed. How do I exclude any open file name Personal.xls when running this code?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Multiple Workbooks to one.
2
Replies
11
Views
523
dmt32
D
O
  • Solved
Copying contents of named range from one workbook to another with identical structure
Replies
2
Views
289
Alex Blakenburg
A
G
  • Question
Open workbooks in folder and run Macro in each book
Replies
7
Views
337
glynn1969
G
B
  • Question
Auto calculation off on single sheet even after saving workbook?
Replies
4
Views
169
Jeffrey Mahoney
Jeffrey Mahoney
G
  • Question
Select an Open workbook and apply a macro1 contained in the new book to the new book
Replies
1
Views
114
LazyBug
LazyBug

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top