List all sheets in current workbook in new workbook

Question

I am attempting to write a sub that will list the sheets in the current workbook (the one with the sub) into a workbook called ListAllSheets but have become a bit stuck on how to access the ListAllSheets to write the names into, this is what I have so far.

Sub ListAllSheets()

    'Create variables
    Dim outputFile As String
    Dim mainworkBook As Workbook

    Set mainworkBook = ActiveWorkbook
    
    'Assign the file to outputFile
    outputFile = "D:\QA\ListAllSheets.xlsx"
    
    On Error Resume Next
    Workbooks("ListAllSheets.xlsx").Close SaveChanges:=False
    On Error GoTo 0
        
    'Delete old file if it exists
    If Len(Dir$(outputFile)) > 0 Then
         Kill outputFile
    End If

    'Create new file
    Workbooks.Add.SaveAs Filename:=outputFile
       
    'List all the sheet in current file into the outputFile
    For i = 1 To mainworkBook.Sheets.Count

        Sheets("Sheet1").Range(“A” & i) = mainworkBook.Sheets(i).Name

    Next i

End Sub

When I run it I either get an error or it lists the sheets in the wrong workbook.

Updated code:

Sub ListAllSheets()

    'Create variables
    Dim outputFile As String
    Dim mainworkBook As Workbook
    Dim newworkBook As Workbook
    
    'Assign the file to outputFile
    outputFile = "D:\QA\ListAllSheets.xlsx"
    
    Set newworkBook = Workbooks(outputFile)
    Set mainworkBook = ActiveWorkbook
  
    On Error Resume Next
    Workbooks("ListAllSheets.xlsx").Close SaveChanges:=False
    On Error GoTo 0
        
    'Delete old file if it exists
    If Len(Dir$(outputFile)) > 0 Then
         Kill outputFile
    End If

    'Create new file
    Workbooks.Add.SaveAs Filename:=outputFile
       
    'List all the sheet in current file into the outputFile
    For i = 1 To mainworkBook.Sheets.Count

        newworkBook.Sheets("Sheet1").Range(“A” & i) = mainworkBook.Sheets(i).Name

    Next i

End Sub

Answer 1

Where it says Sheets("Sheet1").Range(“A” & i) = mainworkBook.Sheets(i).Name, consider specifying which workbook to write to, by putting the new workbook into a variable (such as newworkBook) and calling newworkBook.Sheets("Sheet1").Range(“A” & i) = mainworkBook.Sheets(i).Name.

That will specify which workbook to output to, which seems to be your issue here.