The code below for some reason says out of context where I am trying to delete then replace tabs.

[Darren Smith]

Option Explicit

Sub CopySheets()

Dim AutomatedCardworkerWorkbook As Workbook
Dim JobCardMasterWorkbook As Workbook
Dim JobCardMasterWorksheets As Worksheet



Set AutomatedCardworkerWorkbook = Workbooks("Automated Cardworker")
Set JobCardMasterWorkbook = Workbooks("1 Page Job Card Master")
Set JobCardMasterWorksheets = JobCardMasterWorkbook.Sheets("Job Card Master")

Application.DisplayAlerts = False

AutomatedCardworkerWorkbook.Sheets("Job Card Master").Delete
AutomatedCardworkerWorkbook.Sheets("Job Card with Time Analysis").Delete
AutomatedCardworkerWorkbook.Sheets("Check Sheet").Delete
AutomatedCardworkerWorkbook.Sheets("SPEC SHEET").Delete
AutomatedCardworkerWorkbook.Sheets("Electrical inspection").Delete
AutomatedCardworkerWorkbook.Sheets("PRE ASSEMBLY3").Delete
AutomatedCardworkerWorkbook.Sheets("TOOLPOD").Delete
AutomatedCardworkerWorkbook.Sheets("FAB SHOP").Delete
AutomatedCardworkerWorkbook.Sheets("STOCK LIST").Delete
AutomatedCardworkerWorkbook.Sheets("ORDER LIST").Delete
AutomatedCardworkerWorkbook.Sheets("PRE-DEL").Delete
AutomatedCardworkerWorkbook.Sheets("WOOD SHOP").Delete
AutomatedCardworkerWorkbook.Sheets("Electrical load analysis").Delete

For Each JobCardMasterWorksheets In JobCardMasterWorkbook.Sheets
JobCardMasterWorksheets.Copy After:=AutomatedCardworkerWorkbook.Sheets(AutomatedCardworkerWorkbook.Sheets.Count)
Next
Application.DisplayAlerts = True
End Sub

 

[JLGWhiz]

Here you set the variable to a specific worksheet.

Set JobCardMasterWorksheets = JobCardMasterWorkbook.Sheets("Job Card Master")

Here you try to use the object as an undedicated variable in a For Each statement.

For Each JobCardMasterWorksheets In JobCardMasterWorkbook.Sheets

The compiler cannot follow that type of logic. You need to use a different undedicated variable in the For Each statement.

I am not sure what your intent is with the For Each statement. If you only want to copy Sheets("Job Card Master") then you do not need the For Each statement, you can just use

JobCardMasterWorksheets.Copy After:=AutomatedCardworkerWorkbook.Sheets(AutomatedCardworkerWorkbook.Sheets.Count)

But if you want to copy all sheets then the variable needs to be changed to something else.

 

[Darren Smith]

Thanks
But I do need to copy all sheets. How can I alter my code to work?

 

[Darren Smith]

I`ve changed it to this but it says subscript out of range.

Please help

Option Explicit

Sub CopySheets()

Dim AutomatedCardworkerWorkbook As Workbook
Dim JobCardMasterWorkbook As Workbook
Dim JobCardMasterWorksheets As Worksheet
Dim AutomatedCardworkerWorksheets As Worksheet


Set AutomatedCardworkerWorkbook = Workbooks("Automated Cardworker.xlsm")
Set JobCardMasterWorkbook = Workbooks("1 Page Job Card Master")
For Each AutomatedCardworkerWorksheets In AutomatedCardworkerWorkbook.Sheets

Dim Ws5 As Worksheet
Dim Ws6 As Worksheet
Dim Ws7 As Worksheet
Dim Ws8 As Worksheet
Dim Ws9 As Worksheet
Dim Ws10 As Worksheet
Dim Ws11 As Worksheet
Dim Ws12 As Worksheet
Dim Ws13 As Worksheet
Dim Ws14 As Worksheet
Dim Ws15 As Worksheet
Dim Ws16 As Worksheet
Dim Ws17 As Worksheet

Set Ws5 = Worksheets("Sheet5")
Set Ws6 = Worksheets("Sheet6")
Set Ws7 = Worksheets("Sheet7")
Set Ws8 = Worksheets("Sheet8")
Set Ws9 = Worksheets("Sheet9")
Set Ws10 = Worksheets("Sheet10")
Set Ws11 = Worksheets("Sheet11")
Set Ws12 = Worksheets("Sheet12")
Set Ws13 = Worksheets("Sheet13")
Set Ws14 = Worksheets("Sheet14")
Set Ws15 = Worksheets("Sheet15")
Set Ws16 = Worksheets("Sheet16")
Set Ws17 = Worksheets("Sheet17")


Application.DisplayAlerts = False

Workbooks("AutomatedCardworkerWorkbook").Ws5.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws6.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws7.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws8.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws9.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws10.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws11.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws12.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws13.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws14.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws15.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws16.Delete
Workbooks("AutomatedCardworkerWorkbook").Ws17.Delete

JobCardMasterWorksheets.Copy After:=AutomatedCardworkerWorkbook.Sheets(AutomatedCardworkerWorkbook.Sheets.Count)
Next
Application.DisplayAlerts = True
End Sub

 

[My Aswer Is This]

Would you explain in words what your objective here is.
It looks like you want to delete a lot of Work sheets.
But then you say:
But I do need to copy all sheets. How can I alter my code to work?

You want to copy all worksheets but at same time delete all worksheets?
Or is there a list of all worksheets you want to delete.
And copy all worksheets to where?

 

[Fluff]

Maybe

Sub CopySheets()

Dim AutomatedCardworkerWorkbook As Workbook
Dim JobCardMasterWorkbook As Workbook
Dim JobCardMasterWorksheets As Worksheet
Dim Ws As Worksheet


Set AutomatedCardworkerWorkbook = Workbooks("Automated Cardworker")
Set JobCardMasterWorkbook = Workbooks("1 Page Job Card Master")
Set JobCardMasterWorksheets = JobCardMasterWorkbook.Sheets("Job Card Master")

Application.DisplayAlerts = False

AutomatedCardworkerWorkbook.Sheets("Job Card Master").Delete
AutomatedCardworkerWorkbook.Sheets("Job Card with Time Analysis").Delete
AutomatedCardworkerWorkbook.Sheets("Check Sheet").Delete
AutomatedCardworkerWorkbook.Sheets("SPEC SHEET").Delete
AutomatedCardworkerWorkbook.Sheets("Electrical inspection").Delete
AutomatedCardworkerWorkbook.Sheets("PRE ASSEMBLY3").Delete
AutomatedCardworkerWorkbook.Sheets("TOOLPOD").Delete
AutomatedCardworkerWorkbook.Sheets("FAB SHOP").Delete
AutomatedCardworkerWorkbook.Sheets("STOCK LIST").Delete
AutomatedCardworkerWorkbook.Sheets("ORDER LIST").Delete
AutomatedCardworkerWorkbook.Sheets("PRE-DEL").Delete
AutomatedCardworkerWorkbook.Sheets("WOOD SHOP").Delete
AutomatedCardworkerWorkbook.Sheets("Electrical load analysis").Delete

For Each Ws In JobCardMasterWorkbook.Sheets
   JobCardMasterWorksheets.Copy After:=AutomatedCardworkerWorkbook.Sheets(AutomatedCardworkerWorkbook.Sheets.Count)
Next Ws
Application.DisplayAlerts = True
End Sub

 

[My Aswer Is This]

Looks like you now have a answer. I can move on. Great to see you have a answer

 

[JLGWhiz]

Use a different variable as shown below. That will prevent the conflict between the variable that represents a specific sheet and the attempt to use that variable as an implicit variable.

Dim sh As Worksheet
For Each sh In JobCardMasterWorkbook.Sheets   'The For Each sets the sh variable on each iteration as you have declared it with the Dim statement
    sh.Copy After:=AutomatedCardworkerWorkbook.Sheets(AutomatedCardworkerWorkbook.Sheets.Count)
Next

 

[Darren Smith]

Would you explain in words what your objective here is. The idea is to first delete all accept the first 4 Sheets in Automated Cardworker. Then add them in again using the all the sheets in job card master workbooks. Which is chosen from the listbox3

It looks like you want to delete a lot of Work sheets.
But then you say:
But I do need to copy all sheets. How can I alter my code to work?

You want to copy all worksheets but at same time delete all worksheets? Delete from Automated Cardworker then add back in with new spread sheet as above
Or is there a list of all worksheets you want to delete. All accept the first 4 tabs on Automated Cardworker
And copy all worksheets to where? Automated Cardworker After the first 4 Tabs

Each workbook in listbox3 has different coding which i need to work with.

 

[My Aswer Is This]

Each workbook in listbox3 has different coding which i need to work with.

I see your having others help you on this question. So I'm sure they will be able to help you. Glad to see your getting help