Trying to use a listbox click to clear the tabs from a workbook except for the first 4 tabs. Then open a workbook and copy all tabs then paste

Status
Not open for further replies.

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
360
Office Version
  1. 2019
Platform
  1. Windows

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??​




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 Worksheet

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

Set shttocopy = wkbSource.Sheets
shttocopy.Copy
wkbDest.Sheets(wkbSource).Select
wkbDest.Paste

Application.DisplayAlerts = True

End If

End Sub
 
Last edited:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,126,944
Messages
5,621,751
Members
415,854
Latest member
Tutu123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top