I want my code to pick up a file (file 2) and then list out all the tabs in that file in my current spreadsheet ("Input_tab" from file1). The code is not making creating the list. What is the error in my code?

Sub ListSheets()

Dim FilePicker As FileDialog
Dim mypath As String
Dim sheet_count As Integer
Dim i As Integer
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets(Sheet1)
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)

    With FilePicker
        .Title = "Please Select a File"
        .ButtonName = "Confirm"
        .AllowMultiSelect = False
        If .Show = -1 Then
            mypath = .SelectedItems(1)
        Else
            End
        End If
    End With
    
Workbooks.Open Filename:=mypath

sheet_count = Sheets.Count

For i = 1 To sheet_count
    ws.Cells(i, 1) = Sheets(i).Name
Next i

ActiveWorkbook.Close savechanges:=False


End Sub

CodePudding user response：

When working with multiple workbooks (or really all the time) you should always be explicit about what (eg) Sheets collection you want to refer to (ie. in which workbook?)

This works for me

Sub ListSheets()
    
    Dim mypath As String
    Dim i As Long 'prefer Long over Integer
    Dim ws As Worksheet, wb As Workbook
    
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    mypath = GetFilePath("Please Select a File", "Confirm")
    If Len(mypath) = 0 Then Exit Sub
    
    Application.ScreenUpdating = False 'hide opening workbook
    Set wb = Workbooks.Open(Filename:=mypath, ReadOnly:=True) 'get a reference to the opened workbook
    For i = 1 To wb.Sheets.Count
        ws.Cells(i, 1) = wb.Sheets(i).Name
    Next i
    wb.Close savechanges:=False

End Sub

'return user-selected file path
Function GetFilePath(TitleText As String, ButtonText As String) As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = TitleText
        .ButtonName = ButtonText
        .AllowMultiSelect = False
        If .Show = -1 Then GetFilePath = .SelectedItems(1)
    End With
End Function