Combining Multilple Worksheets Into One

[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 A1355.

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

The macro would then go to the next worksheet, and pull A1355 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.

 

[Claus Busch]

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 A1355.

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

The macro would then go to the next worksheet, and pull A1355 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("A1355").Copy _
Destination:=Sheets("Combined").Range("A" & LRow)
End If
End With
Next
End Sub


Regards
Claus Busch

 

[djc]

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,

 

[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).

Thank you again. djc

 

[Claus Busch]

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("A1355").Copy
Sheets("Combined").Range("A" & LRow) _
.PasteSpecial xlPasteValues
End If
End With
Next
End Sub


Regards
Claus Busch

 

[djc]

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

 

[magmike]

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 A1355. I would like to extract A1355 from the first worksheet “1” and place it in a sheet called “Combined”. The macro would then go to the next worksheet, and pull A1355 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 beautifullyand is flexible to work in different scenarios. I use it often and it's free:
http://www.rondebruin.nl/merge.htm