search

fjtello / SQL-C-sharp

Common programming and coding tips and howtos
0 stars 0 forks source link

Excel - Get all tab names via macro sub [#excel #macro #vb] #47

Open fjtello opened 5 years ago

fjtello commented 5 years ago

Sub ListSheetNamesInNewWorkbook() Dim objNewWorkbook As Workbook Dim objNewWorksheet As Worksheet

Set objNewWorkbook = Excel.Application.Workbooks.Add
Set objNewWorksheet = objNewWorkbook.Sheets(1)

For i = 1 To ThisWorkbook.Sheets.Count
    objNewWorksheet.Cells(i, 1) = i
    objNewWorksheet.Cells(i, 2) = ThisWorkbook.Sheets(i).Name
Next i

With objNewWorksheet
     .Rows(1).Insert
     .Cells(1, 1) = "INDEX"
     .Cells(1, 1).Font.Bold = True
     .Cells(1, 2) = "NAME"
     .Cells(1, 2).Font.Bold = True
     .Columns("A:B").AutoFit
End With

End Sub

excel #macro