I have a workbook with a sheet for each month of the year. I want to combine column A from the all the monthly Sheets but not the other three sheets (Event Totals, Monthly Totals, and Vendors 2017) into one sheet on the Event Totals sheet. I have included my code below. I am currently getting an error on If sh.Name <> "Event Totals" Or "Monthly Totals" Or "Vendors 2017" Then as well as Last = LastRow(sumSht). I would also like to add some code to the CopyRng to only copy the cells in column A from the monthly sheets which have data.
Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim sumSht As Worksheet
Dim i As Long
Dim CopyRng As Range
Set sumSht = Sheets("Event Totals")
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Event Totals" Or "Monthly Totals" Or "Vendors 2017" Then
Last = LastRow(sumSht)
Set CopyRng = sh.Range("A1:A25")
If Last + CopyRng.Rows.Count > sumSht.Rows.Count Then
MsgBox "There are not enough rows in the sumSht"
GoTo ExitTheSub
End If
CopyRng.Copy
With sumSht.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
Next
ExitTheSub:
Application.Goto sumSht.Cells(1)
sumSht.Columns.AutoFit
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Bookmarks