Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Multilple Worksheets Into One
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Multilple Worksheets Into One
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Multilple Worksheets Into One
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Multilple Worksheets Into One
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Multilple Worksheets Into One
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Multilple Worksheets Into One
I added just the PasteSpecial line and it the previous macro and it worked perfect! Thank you!
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combining Multilple Worksheets Into One
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining worksheets | Excel Worksheet Functions | |||
Combining worksheets | New Users to Excel | |||
Help searching and summing across multilple worksheets | Excel Discussion (Misc queries) | |||
Combining 2 or more worksheets | Excel Worksheet Functions | |||
combining two worksheets into one | Excel Programming |