Combine sheets into one sheet - Urgent

[bubba1965]

I am in urgent need of figuring out a way to combine multiple sheet
from one workbook into one combined sheet. I have several workbook
with each containing about 50 worksheets. I need to combine eac
workbook into one sheet with the last sheet the first thing listed o
the new workbook and the second to last being the second, etc.

Each sheet contains the same number of columns.

Is this possible, please help? I am under deadline

 

[Bernie Deitrick]

Bubba,

Copy the code below into a module in a blank workbook, then run it and
select the workbook that you want to combine onto one sheet.

Assumes: table starts in A1, and is contiguous.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

Set Basebook = ThisWorkbook

Set myBook = Workbooks.Open(Application.GetOpenFilename)
For Each mySheet In myBook.Worksheets
mySheet.Activate
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
Next mySheet
myBook.Close

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub

 

[bubba1965]

Thanks for your reply. That is very helpful

Is there a way to have it start with the final sheet on the right an
work its way back to the first sheet (all the way to the left).

Also, how can I save this macro in Excel. I am new to macros - I hav
saved a few in Word. But I wasn't successful with Excel.

To run your macro, I went to Tools- Macros - Visual Basic Editor, the
I went to insert Module, copied your code and hit run. Is this th
easiest way to run a macro and if so - how do I save the macro fo
future use.

I appreciate your help very much. Thank yo

 

[Bernie Deitrick]

Bubba,

Record a new macro (doens't matter what), and indicate that it should be
stored in "Personal.xls". That will create a library file called
Personal.xls that will always be available. However, the macro as written
would need to be modified slightly to run from Personal.xls. If you want to
go that route, then post back.

To go backwards, then you would need to change:

For Each mySheet In myBook.Worksheets
mySheet.Activate
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
Next mySheet

TO:

For i = myBook.Worksheets.Count To 1 Step -1
myBook.Worksheets(i).Activate
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("a65536").End(xlUp).Offset(1, 0)
Next i

HTH,
Bernie
MS Excel MVP

 

[bubba1965]

Thanks so much for your help.

Yes I would like to be able to go that route and record the macro fo
future use. If you could help me with that, I would really, reall
appreciate.

Thanks agai

 

[Bernie Deitrick]

bubba,

to create your own Personal.xls, use Tools | Macro... Record New Macro...
then change "Store macro in:" to "Personal Macro Workbook" and then click
OK. Then click on the stop recording macro. Go into the VBE (Alt-F11) and
open the project explorer (Ctrl-R). Then select the Personal.xls workbook,
and select Insert | Module and paste the code into the module. Then press
the save icon.

The only thing that you would need to change in the macro to have it be
compatible with being run from Personal.xls is the line:

Set Basebook = ThisWorkbook

Change it to:

Set Basebook = ActiveWorkbook

Then to use the macro, you would need to open a new blank workbook prior to
starting the macro using Tools | Macro | Macros....

HTH,
Bernie
MS Excel MVP

 

[bubba1965]

Hi Bernie,

I thought I went through all of the steps you outlined. But nothin
happens when I hit run. Do I name the macro before in the area where
select "Store macro in:"

Is every new Excel document I create call Personal by default.

I changed Set Basebook = This Workbook
to
Set Basebook = ActiveWorkbook

To run the macro I selected Tools - Macro - Macro and then selected th
new Macro I named Combine_sheets and hit run, but nothing transpired.

What am I doing wrong

 

[Bernie Deitrick]

Bubba,

Try navigating within the macro window to find your macro. When you use
Tools | Macro | Macros.... change the value in the "Macros In:" dropdown to
"personal.xls", then select your newly named macro, then hit the "Run"
button.

HTH,
Bernie
MS Excel MVP

 

[bubba1965]

Thanks Bernie

I think after all this, I would just like to do it the first way yo
described. But now I can not do this. How do I get rid of th
Personal.xls - so that it doesn't show up in every new document b
default.

I will just copy and post in the macro when I need it and run it fro
there in VBE.

Sorry to keep bothering you with us

 

[Bernie Deitrick]

bubba,

Do a find from windows, looking for personal.xls, and then just delete any
copies you do discover. Excel doesn't care if the file is deleted that way
unless you have macro buttons that reference it, which you don't.

HTH,
Bernie
MS Excel MVP