CODEWITHSUNDEEP
vbaexcel
luke
luke

Reputation: 482

Create list of all sheet names in workbook

I have this code that creates a new sheet in the workbook and lists all the sheet names in a column, which work fine but I would like it to leave 27 empty rows between each sheet name

Dim xWs As Worksheet

On Error Resume Next

Application.DisplayAlerts = False

xTitleId = "All Sheet Names"

Application.Sheets(xTitleId).Delete

Application.Sheets.Add.index

Set xWs = Application.ActiveSheet

xWs.Name = xTitleId

For i = 2 To Application.Sheets.Count
    xWs.Range("A" & (i - 1)) = Application.Sheets(i).Name
Next

Example this is how it lists them now enter image description here

And this is how i want it to list them with the empty spaces in between each sheet name enter image description here

Upvotes: 2

Views: 1900

Answers (1)

user6432984
user6432984

Reputation:

The OP said 27 blank rows between them, so the next row will be 28 rows down.

enter image description here

For i = 2 To Application.Sheets.Count

    xWs.Range("A" & ((i - 1) * 28) - 27) = Application.Sheets(i).Name

Next

Upvotes: 3

Related Questions