I want to merge these 2 worksheets together. I will have more to merge later on also. What is a macro that I can use to make this simple?
I want to merge these 2 worksheets together. I will have more to merge later on also. What is a macro that I can use to make this simple?
Please elaborate a little more on what exactly has to be done. There are more columns filled in Waffle Products than in Crutches. Which goes first?
I actually need to merge these sheets horizontally and vertically. Is that possible? And there will be more sheets added as I clean up sheets. It really doesn't matter which one goes first, I just need to have them merge together.
This was posted on an earlier forum. And it worked great vertically, but all of my data horizontally got grouped under each other.
---------------------------------------------------------------------------------------------------------------------------
Re: Merge worksheets into one within same workbook
The key would need to be on each sheet, but 200!? How unmanageable that must be!
No, with 200 sheets, I would abandon this and use an "on-demand macro" to assemble my "consolidated sheet". I wouldn't expect to be able to do this in realtime.
Here's a macro that will do it for you:
Option Explicit
Sub ConsolidateSheets()
'JBeaucaire (6/26/2009)
'Merge all sheets in a workbook into one summary sheet (stacked)
Dim cs As Worksheet, ws As Worksheet, LR As Long, NR As Long
Application.ScreenUpdating = False
If Not SheetExists("Consolidate") Then _
Worksheets.Add(Before:=Sheets(1)).Name = "Consolidate"
Set cs = Sheets("Consolidate")
cs.Cells.Clear
Sheets(2).Rows(1).Copy cs.Range("A1")
NR = 2
For Each ws In Worksheets
If ws.Name <> "Consolidate" Then
ws.Activate
LR = Range("A1").SpecialCells(xlCellTypeLastCell).Row
Range("A2:AA" & LR).Copy
cs.Range("A" & NR).PasteSpecial xlPasteValues
Application.CutCopyMode = False
NR = cs.Range("A" & Rows.Count).End(xlUp).Row + 1
End If
Next ws
cs.Activate
Columns("A:AA").AutoFit
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Public Function SheetExists(SName As String, Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End FunctionHow to use the macro:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks