Combine Worksheets into One Master

[Anne Troy]

Great procedure. Thanks, smozgur!

This macro takes the used range from all worksheets (we're assuming they all have the exact same layout) and combines them into a new worksheet called "Master".

I want to add it to our knowledgebase, but I think we ought to add a little message box that asks if the worksheets have heading rows, because most will, and then edit the code accordingly so that it doesn't pull the first row over in that case. Either that, or we make 2 KB entries, one for each scenario.

Here's the current code:

Sub CopyUsedRangeValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
    MsgBox "A worksheet called Master already exists"
    Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
    If sh.Name <> DestSh.Name Then
        If sh.UsedRange.Count > 1 Then
            Last = LastRow(DestSh)
            With sh.UsedRange
                DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
                .Columns.Count).Value = .Value
            End With
        End If
    End If
Next
Application.ScreenUpdating = True
End Sub
 
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
 
Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
 
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(Sheets(SName).Name))
End Function

Current sample attached.