Code to copy and paste all data from specified workbook

[Darren Smith]

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.

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

 

[RoryA]

OK, I've updated your thread title, added code tags and added your comments to your original post.

So, a couple of questions:

- is the workbook with the code in it the "Automated Cardworker.xlsm" workbook?
- is the listbox multi-select or single?

 

[Darren Smith]

OK, I've updated your thread title, added code tags and added your comments to your original post.

So, a couple of questions:

- is the workbook with the code in it the "Automated Cardworker.xlsm" workbook? Yes correct
- is the listbox multi-select or single? Single

Thanks

 

[NdNoviceHlp]

What exactly is in listbox3? The name of the wb or the whole file path? Are the listbox3 wbs in the same folder as the active wb? Dave

 

[Darren Smith]

What exactly is in listbox3? The name of the wb or the whole file path? Are the listbox3 wbs in the same folder as the active wb? Dave

Sorry this is a mess it's just the name of the workbooks i think the file path should be there. The list box workbooks are in a separate folder.

 

[NdNoviceHlp]

Well what's your plan? U can't get info from a wb file if U don't specify the entire path. U could put the whole path(s) in the listbox, or the whole path(s) in a worksheet, or hard code the path(s) in vba, or search all directories for a "fuzzy" match which may crash dependent upon how unique the wb names are. I'm guessing that using a blank column to list entire wb filepaths and then loading only the wb names to your listbox would be easiest to manage. Can't really code anything until U decide. HTH. Dave

 

[Darren Smith]

I think your right please do it the way you said.

 

[NdNoviceHlp]

Sorry Darren... after a re-read of your objective, you want to delete all of your sheets... it seems like that wouldn't work. How are U loading your listbox to begin with anyways? Dave

 

[Darren Smith]

Sorry Darren... after a re-read of your objective, you want to delete all of your sheets... it seems like that wouldn't work. How are U loading your listbox to begin with anyways? Dave

I load it from additem in my code.

 

[NdNoviceHlp]

Yes but where are the wb names stored... in the VBA code or elsewhere? Dave