Excel VBA Macro: List All Sheet/Tab Names (From Another User Selected Workbook)

Показать описание

Excel VBA Macro: List All Sheet/Tab Names (From Another User Selected Workbook). In this video, we go over how to prompt the user to select another excel file, and then list all of the sheet/tab names in that file.

Code (just realized I never used the "sheet_name" variable. that can be omitted):

Sub tab_names_from_another_wb()

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

Application.ScreenUpdating = False

Set ws = ThisWorkbook.Sheets(1)
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

ws.Cells.ClearContents
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

Application.ScreenUpdating = True

End Sub

#ExcelVBA #ExcelMacro