This code will not open the required worksheet selection in the listbox. I`ve underlined the code that`s failing below

[Darren Smith]

This code will not open the required workbook selection in the listbox. I`ve underlined the code that`s failing below​

Private Sub ListBox3_Click()

    Dim ObjWorksheet As Object, Sht As Worksheet, PageCollect As Collection
    Dim FSO As Object, X As Object, FilDir As Object, Fil As Object, Cnt As Integer
    Dim wkbDest As Workbook
    Dim wkbSource As Workbook
    Dim ws As Worksheet
  
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
  
    Set FSO = CreateObject("Scripting.FilesystemObject")
    Set wkbDest = Workbooks("Automated Cardworker.xlsm")
    Set wkbSource = FSO.GetFile("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Jobcard Templates\" _
                         & UserForm1.ListBox3.List(UserForm1.ListBox3.ListIndex))

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



    Workbooks.Open Filename:=wkbSource
Set PageCollect = New Collection
For Each Sht In Workbooks(wkbSource.Name).Sheets
PageCollect.Add Workbooks(wkbSource.Name).Sheets(Sht.Name)
Next Sht

For Cnt = 1 To PageCollect.Count
PageCollect(Cnt).Copy wkbSource.Sheets(Cnt)
Next Cnt
Workbooks(wkbSource.Name).Close SaveChanges:=False
Set wkbSource = Nothing
wkbDest.Worksheets(ObjWorksheet).Delete


Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True



Set FSO = Nothing

End Sub

 

[RoryA]

You can't use GetFile to open a workbook. You need to use Workbooks.Open

 

[Darren Smith]

Thanks for that
The code above is not copying over tabs from specified workbook to "Automated coardworker" can you help me.

 

[RoryA]

Assuming you want to copy the actual sheets themselves:

  Do While wkbDest.Sheets.Count > 4
        wkbDest.Sheets(wkbDest.Sheets.Count).Delete
    Loop
    Set wkbSource =Workbooks.Open("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\DLS Cardworker\Jobcard Templates\" _
                         & UserForm1.ListBox3.List(UserForm1.ListBox3.ListIndex))

For Each Sht In Workbooks(wkbSource.Name).Sheets
   Sht.Copy After:=wkbDest.Sheets(wkbDest.Sheets.Count)
Next Sht

wkbSource.Close SaveChanges:=False

 

[Darren Smith]

All working fine
Thankyou very much sorry to be so thick

 

[RoryA]

Not at all - glad we could help.