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

 

[Darren Smith]

Trying to use a listbox click to clear the tabs from a active workbook except for the first 4 tabs. Then open a new workbook from listbox item and copy all tabs then paste into the active workbook. Code below clears all tabs except the first 4 then opens the correct workbook but does not copy and paste?? Please help.


Private Sub ListBox3_Click()

For i = 0 To Me.ListBox3.ListCount - 1

If Me.ListBox3.Selected(i) Then

Workbooks.Open "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Jobcard Templates\" & Me.ListBox3.List(i)

Exit For

End If

Next i

Dim wkbDest As Workbook
Dim wkbSource As Workbook
Dim sht As Worksheets

Set wkbDest = Workbooks("Automated Cardworker.xlsm")

If Me.ListBox3.ListIndex > -1 Then
Set wkbSource = Workbooks(Me.ListBox3.Value)

Application.DisplayAlerts = False

Do While wkbDest.Sheets.Count > 4
wkbDest.Sheets(wkbDest.Sheets.Count).Delete
Loop

For Each sht In wkbSource.Sheets
wkbSource.Copy After:=wkbDest.Sheets(wkbDest.Sheets.Count)

Application.DisplayAlerts = True

Next

End If

End Sub

 

[NdNoviceHlp]

"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"
That's what the posted code does! I'm done. Dave

 

[Darren Smith]

I have tried your code out it puts a Temp tab in the workbook but not the tabs from specified workbook.
I know your code will work I just don't understand the reason for the Temp Tab. Please help?

 

[NdNoviceHlp]

Darren U can't delete all the sheets from a wb which is what U had requested. So, the 1 sheet gets renamed "temp", all the sheets from the other wb get imported and then the renamed "temp" sheet is deleted (because it couldn't be deleted to start with). The code was tested and works. If the code is not importing sheets then you have the file name/path wrong or U did not adjust the name of the userform as required. If U don't want to delete all the sheets and/or U don't want to import all of the sheets from the import wb then that's quite a bit different and perhaps U may understand my frustration with wasting time and effort on an erroneous solution. You need to identify what the sheet names are that U don't want deleted and confirm that U want all the worksheets imported. Dave

 

[Darren Smith]

I have done what you said above it now works thank you for your help.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Be safe. Dave