Windows 7 - Combining Multilple Worksheets Into One

Asked By djc on 09-Aug-12 12:51 PM
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 n=
eed to extract only the data in the range A13:P55.

I would like to extract A13:P55 from the first worksheet =931=94 and place =
it in a sheet called =93Combined=94.=20

The macro would then go to the next worksheet, and pull A13:P55 and put in =
that range in the =93Combined=94 tab below the data it from the previous wo=
rksheet. =20

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

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




Claus Busch replied to djc on 09-Aug-12 01:25 PM
hi,

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


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
djc replied to Claus Busch on 09-Aug-12 01:59 PM
Hi Claus, Thank you for the quick reply.  It works quickly, however it seem=
s to be missing column A when it combines. It puts column B from the worksh=
eets into column A of the Combined tab.  Column A is a formula. Does that h=
ave anything to do with?  Ideally, all the numbers and text come in are val=
ues and not formulas.

Thanks again,
djc replied to djc on 09-Aug-12 02:06 PM
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 fi=
rst 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).=20

Thank you again.  djc
Claus Busch replied to djc on 09-Aug-12 02:18 PM
Hi,

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


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
djc replied to Claus Busch on 09-Aug-12 02:44 PM
I added just the PasteSpecial line and it the previous macro and it worked perfect!  Thank you!
magmike replied to djc on 09-Aug-12 03:35 PM
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 ho=
wever). The good news is that each sheet is a template and I need to extrac=
t only the data in the range A13:P55. I would like to extract A13:P55 from =
the first worksheet =931=94 and place it in a sheet called =93Combined=94. =
The macro would then go to the next worksheet, and pull A13:P55 and put in =
that range in the =93Combined=94 tab below the data it from the previous wo=
rksheet. This process would go until it completes all 300+ worksheets. Is t=
his 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 is fr=
ee:
http://www.rondebruin.nl/merge.htm