I have the below macro which lists all the worksheet names in the current(host) workbook.
I would like to adapt this code so it targets an active/open workbook, which has its workbook name & extension referenced in cell C1.
range("C1").value
Please can someone let me know how I adapt the below code.
Sub ListSheets()Dim ws As WorksheetDim x As Integerx = 1Sheets("Sheet1").Range("A:A").ClearFor Each ws In WorksheetsSheets("Sheet1").Cells(x, 1) = ws.Namex = x + 1Next wsEnd SubBest Answer
This should do the job but you don't need the extension of the workbook just the name.
Sub ListSheets()Dim ws As WorksheetDim x As IntegerDim wbk As WorkbookDim wbkName As Stringx = 1wbkName = ThisWorkbook.Sheets("Sheet1").Range("C1").ValueThisWorkbook.Sheets("Sheet1").Range("A:A").ClearSet wbk = Application.Workbooks(wbkName)For Each ws In wbk.WorksheetsThisWorkbook.Sheets("Sheet1").Cells(x, 1) = ws.Namex = x + 1Next wsEnd SubSub ListSheets()Dim inputwb As WorkbookDim ws As Worksheet, source As WorksheetDim LRow As Long' Change source sheet if it is NOT always the active worksheet' or activate the source sheet first with Workbooks("NAME").Sheets(INDEX).Activate (not preferable)Set source = ActiveSheetOn Error Resume NextSet inputwb = Workbooks(Cells(1, 1).Value)If err.Number <> 0 ThenMsgBox "Could not find workbook " & Cells(1, 1).Value & ". Subroutine execution stopped."Exit SubEnd IfOn Error GoTo 0ThisWorkbook.Sheets("Sheet1").Range("A:A").ClearLRow = 1For Each ws In inputwb.WorksheetsThisWorkbook.Sheets("Sheet1").Cells(LRow, 1) = ws.NameLRow = LRow + 1Next wsEnd Sub