Compiling Worksheets from the Same Workbook

[Alex89]

Hi Everyone,

I'm trying to compile 6 worksheets from the same workbook, into 2 separate worksheets. In this instance I am trying to copy data from the 3 publisher content tabs into a master sheet, but I am getting an error with this line of code

For Each ws In ThisWorkbook.Worksheets("Publisher - Content", "Publisher - Content (2)", "Publisher - Content (3)")


Sub Combined()


Dim ws As Worksheet
Dim shtMaster As Worksheet
Dim rTargetLastCell As Range
Dim rSourceLastCell As Range


Set shtMaster = ThisWorkbook.Worksheets("Master Data")


'Cycle through each worksheet in the workbook.
'NB: Worksheets exclude chart sheets and macro sheets.
For Each ws In ThisWorkbook.Worksheets("Publisher - Content", "Publisher - Content (2)", "Publisher - Content (3)")
Select Case ws.Name
Case "Master Data"
'Do Nothing
Case Else
'Find the last cell containing data in the two sheets.
Set rTargetLastCell = LastCell(ThisWorkbook.Worksheets("Master Data"))
Set rSourceLastCell = LastCell(ws)


'Copy and paste the relevant data.
With ws
.Range(.Cells(3, 1), rSourceLastCell).Copy _
Destination:=shtMaster.Cells(rTargetLastCell.Row + 1, 1)
End With
End Select
Next ws


End Sub

 

[JoeMo]

Haven't looked at the rest of your code, but try changing the errant line to this:

For Each ws In ThisWorkbook.Worksheets(Array("Publisher - Content", "Publisher - Content (2)", "Publisher - Content (3)"))

 

[Fluff]

Hi & welcome to MrExcel.
Try

For Each ws In ThisWorkbook.Worksheets(Array("Publisher - Content", "Publisher - Content (2)", "Publisher - Content (3)"))