Use a for loop to add new sheets in workbook

Question

I am trying to write a For...Next loop to create a set number of Worksheets in a Workbook. The the number of worksheets is set by the user from a dashboard, at an earlier point.

Can somebody point me in the right direction? This is my code, so far:

For i = 1 To siteCount
    'I know the below won't work, and I also tried site_ & i, but no luck
    Set site_i = Sheets.Add(after:=Sheets(Worksheets.Count))
    site_i.Name = "Sheet Name"
Next i

I publicly declared the max number of sheets that could be used...in `site_1`, `site_2`, ......`site_n`. — Rivers31334, Nov 04 '16 at 13:57
I think you should better try to use an array instead of dynamic variable names (which does not work in VBA) — Yannici, Nov 04 '16 at 13:58
Thanks. That seems to be the way to go. I'll let you know how it turns out. — Rivers31334, Nov 04 '16 at 13:59

score 5 · Accepted Answer · answered Nov 04 '16 at 13:58

5

With some small adjustments, your code will basically work:

Option Explicit

Sub AddSheets()

    Dim siteCount As Integer
    Dim i As Integer
    Dim site_i As Worksheet

    siteCount = 4

    For i = 1 To siteCount
        Set site_i = Sheets.Add(after:=Sheets(Worksheets.Count))
        site_i.Name = "Sheet_Name_" & CStr(i)
    Next i

End Sub

answered Nov 04 '16 at 13:58

Robin Mackenzie

18,801
7
38
56

The above will work perfect and all but the declaration and assigning of `site_i` is pointless (I realize you're just working from the OP's code but I'd suggest something like `Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Sheet_Name_" & CStr(i)` (and then deleting the second line of the iteration – Jeremy Nov 04 '16 at 14:05
1

@Jeremy thanks. I noticed that myself, but his logic is better. I will mark it as accepted. – Rivers31334 Nov 04 '16 at 14:07

Use a for loop to add new sheets in workbook

1 Answers1

Linked