CODEWITHSUNDEEP
vbaexcel
Marc van der Peet
Marc van der Peet

Reputation: 343

Print sheetnames on separate sheet

I have created some VBA code through which I create a list of all sheet names in my excel. Now I would like to print all the values in sheet "Blad2" in cell A1, A2, A3 etc...

Anybody some thoughts on how I can do this? Already got code below working but then its just overwriting the code in cell A2...

Sub namesSheet()

For i = 1 To Sheets.Count
    Var = Sheets(i).Name
    Worksheets("Blad2").Range("A2") = Var
Next i


End Sub

Upvotes: 1

Views: 60

Answers (3)

psychicebola
psychicebola

Reputation: 949

you have to set the counter for the cell too Sub namesSheet()

For i = 1 To Sheets.Count
    Var = Sheets(i).Name
    Worksheets("Blad2").cells(i,1) = Var
Next i


End Sub

Upvotes: 3

DiegoAndresJAY
DiegoAndresJAY

Reputation: 706

Sub namesSheet()

For i = 1 To Sheets.Count
    Var = Sheets(i).Name
    Worksheets("Blad2").Range("A" & i) = Var
Next i


End Sub

Upvotes: 3

Maciej Los
Maciej Los

Reputation: 8591

Try this:

For i = 1 To Sheets.Count
    Worksheets("Blad2").Range("A1").Offset(RowOffset:=i) = Sheets(i).Name
Next i

Upvotes: 4

Related Questions