I have an issue I think may be easy to solve, but I am a novice with VBA. I need macro that can combine multiple sheets into one sheet.

I have 300+ sheets all named 1-300+ (there are a few missing sheets within the range however). The good news is that each sheet is a template and I need to extract only the data in the range A13:P55.

I would like to extract A13:P55 from the first worksheet “1” and place it in a sheet called “Combined”.

The macro would then go to the next worksheet, and pull A13:P55 and put in that range in the “Combined” tab below the data it from the previous worksheet.

This process would go until it completes all 300+ worksheets.
Is this possible?

Any feedback would be most helpful. Thank you for your time.

hi,

Am Thu, 9 Aug 2012 09:51:38 -0700 (PDT) schrieb djc:

I have an issue I think may be easy to solve, but I am a novice with VBA. I need macro that can combine multiple sheets into one sheet.

I have 300+ sheets all named 1-300+ (there are a few missing sheets within the range however). The good news is that each sheet is a template and I need to extract only the data in the range A13:P55.

I would like to extract A13:P55 from the first worksheet ?1? and place it in a sheet called ?Combined?.

The macro would then go to the next worksheet, and pull A13:P55 and put in that range in the ?Combined? tab below the data it from the previous worksheet.

try:
Sub Combine()
Dim LRow As Long
Dim wsh As Worksheet

For Each wsh In ThisWorkbook.Worksheets
LRow = Sheets("Combined").Cells(Rows.Count, 1).End(xlUp).Row + 1
With wsh
If .Name < "Combined" Then
.Range("A13:P55").Copy _
Destination:=Sheets("Combined").Range("A" & LRow)
End If
End With
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Claus, Thank you for the quick reply. It works quickly, however it seems to be missing column A when it combines. It puts column B from the worksheets into column A of the Combined tab. Column A is a formula. Does that have anything to do with? Ideally, all the numbers and text come in are values and not formulas.

Thanks again,

Hi Claus,I did some more research, There were other roll up tabs that were skewing the data. It worked well. I failed to mention the formulas in my first post. I would like the macro to return values only and not any formulas (my ultimate goal is to get this into a flat file).

Thank you again. djc

Hi,

Am Thu, 9 Aug 2012 11:06:05 -0700 (PDT) schrieb djc:

Hi Claus,I did some more research, There were other roll up tabs that were skewing the data. It worked well. I failed to mention the formulas in my first post. I would like the macro to return values only and not any formulas (my ultimate goal is to get this into a flat file).

then try:
Sub Combine()
Dim LRow As Long
Dim wsh As Worksheet

For Each wsh In ThisWorkbook.Worksheets
LRow = Sheets("Combined").Cells(Rows.Count, 1).End(xlUp).Row + 1
With wsh
If .Name < "Combined" Then
.Range("A13:P55").Copy
Sheets("Combined").Range("A" & LRow) _
.PasteSpecial xlPasteValues
End If
End With
Next
End Sub


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I added just the PasteSpecial line and it the previous macro and it worked perfect! Thank you!

On Thursday, August 9, 2012 11:51:38 AM UTC-5, djc wrote:
I have an issue I think may be easy to solve, but I am a novice with VBA. I need macro that can combine multiple sheets into one sheet. I have 300+ sheets all named 1-300+ (there are a few missing sheets within the range however). The good news is that each sheet is a template and I need to extract only the data in the range A13:P55. I would like to extract A13:P55 from the first worksheet “1” and place it in a sheet called “Combined”. The macro would then go to the next worksheet, and pull A13:P55 and put in that range in the “Combined” tab below the data it from the previous worksheet. This process would go until it completes all 300+ worksheets. Is this possible? Any feedback would be most helpful. Thank you for your time.

Ron De Bruin has created a fantastic Add-In that does just this beautifully and is flexible to work in different scenarios. I use it often and it's free:
http://www.rondebruin.nl/merge.htm