It is not clear to me what your requirements are, but try this one and can be improved
Sub CombineAllSheets1()
Dim ms As Worksheet, ws As Worksheet, LR As Long, i As Long,LRms As Long
Application.ScreenUpdating = 0
Application.DisplayAlerts = 0
On Error Resume Next
If Not Evaluate("ISREF(Master!A1)") Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Master"
Else
Set ms = Sheets("Master")
Sheets("Master").Range("A2:C" & Rows.Count).ClearContents
End If
Worksheets(1).Range("A1").Resize(, 3).Copy ms.Range("A1").Resize(, 3)
For Each ws In Worksheets
With ws
If .Name <> "Master" And .Name <> "Other Sheets" Then
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
.Range("A2:C" & LR).Copy
ms.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
End With
Next ws
For Each ws In Worksheets
With ws
If .Name <> "Master" And .Name <> "Other Sheets" Then
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
.Range("E2:H" & LR).EntireRow.Delete
End If
End With
Next ws
With ms
LRms = ms.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For i = LRms To 2 Step -1
If .Cells(i, "A") = "" Or .Cells(i, "A") = "-" Then
Rows(i).EntireRow.Delete
End If
.Columns.AutoFit
Next i
End With
Application.CutCopyMode = 0
Application.ScreenUpdating = 1
Application.DisplayAlerts = 1
End Sub
Bookmarks