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

--
Message posted from http://www.ExcelForum.com

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).O ffset(1, 0)
Next mySheet
myBook.Close

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

Basebook.SaveAs Application.GetSaveAsFilename

End Sub



"bubba1965 " wrote in message
...
I am in urgent need of figuring out a way to combine multiple sheets
from one workbook into one combined sheet. I have several workbooks
with each containing about 50 worksheets. I need to combine each
workbook into one sheet with the last sheet the first thing listed on
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.


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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).O ffset(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).O ffset(1, 0)
Next i

HTH,
Bernie
MS Excel MVP

"bubba1965 " wrote in message
...
Thanks for your reply. That is very helpful

Is there a way to have it start with the final sheet on the right and
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 have
saved a few in Word. But I wasn't successful with Excel.

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

I appreciate your help very much. Thank you


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

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 " wrote in message
...
Thanks so much for your help.

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

Thanks again


---
Message posted from http://www.ExcelForum.com/