Darren Smith
Well-known Member
- Joined
- Nov 23, 2020
- Messages
- 631
- Office Version
- 2019
- Platform
- Windows
I need to first clear all worksheets in active workbook.
Then use the listbox item to open a new specified Workbook.
Then copy all the sheets paste to original Workbook then close the workbook down.
Keeping the original workbook open.
Then use the listbox item to open a new specified Workbook.
Then copy all the sheets paste to original Workbook then close the workbook down.
Keeping the original workbook open.
Code:
Option Explicit
Sub CopySheets()
Dim AutomatedCardworkerWorkbook As Workbook
Dim JobCardMasterWorkbook As Workbook
Dim JobCardMasterWorksheets As Worksheet
Dim Ws As Worksheet
Dim wb As Workbook
Dim strArray As String
For counter = 0 To ListBox3.Items.Count - 1
strArray(counter) = ListBox3.Items(counter)
Set wb = Workbooks(ListBox3.Value)
Set AutomatedCardworkerWorkbook = Workbooks("Automated Cardworker.xlsm")
Set JobCardMasterWorkbook = strArray
Set JobCardMasterWorksheets = JobCardMasterWorkbook.Sheets
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
Ws.Copy After:=AutomatedCardworkerWorkbook.Sheets(AutomatedCardworkerWorkbook.Sheets.Count)
Next Ws
Application.DisplayAlerts = True
End Sub
Last edited by a moderator: