Error creating create sheets with for loop in Excel

Question

As part of bigger Macro at some point I have to create a certain numbers of sheets in certain position.

To do that and, as I am a begginer with macros (and programming in general) I have done a test with a simple scenario.

I have seen a similar question here

But, adapting that code to my case I'm only able to create the first sheet and getting an error in the following.

The code right now is this:

Sub AddSheets()

Dim siteCount As Integer
Dim i As Integer

siteCount = 2


For i = 1 To siteCount
    Name = Cells(i, 26)
    Set site_i = Sheets.Add(after:=Sheets("DLC_" & CStr(Name)))
    site_i.Name = "DLC" & CStr(Name)

Next i
End Sub

What I'm not seeing?

What is the error? On which line? – Vityata Feb 08 '19 at 12:50 — Vityata, Feb 08 '19 at 12:50

score 0 · Accepted Answer · answered Feb 08 '19 at 13:02

If your idea is to add worksheets to a workbook, based on the values of cells Z1 and Z2, this is a possible solution. First, make sure you actually have some values in Z1 and Z2, in the first worksheet of your workbook.

Z1 and Z2 are used, because they are referred in the original question here: Name = Cells(i, 26). The Cells() takes first the row and then the column. Then run this code:

Option Explicit

Sub AddSheets()

    Dim siteCount As Long
    Dim i As Long
    Dim name  As String
    siteCount = 2

    Dim newWks As Worksheet

    For i = 1 To siteCount
        With Worksheets(1)
            name = .Cells(i, 26)
            If .Cells(i, 26) <> "" Then
                Set newWks = Worksheets.Add(after:=Worksheets(Worksheets.Count))
                newWks.Name = .Cells(i, 26)
            Else
                MsgBox Cells(i, 26).Address & "is empty"
            End If
        End With
    Next i

End Sub

The worksheets are added with: Set newWks = Worksheets.Add(after:=Worksheets(Worksheets.Count))

Their name is changed with newWks.Name = .Cells(i, 26)

How to Add a Named Sheet at the end of all excel sheets

If you run the code twice, you would get an error, because the worksheets with the corresponding names would exist already in the workbook.

... and @Carlos shouldn't use VBA-internal terms like "name" as variable name. — Asger, Feb 08 '19 at 13:08

Error creating create sheets with for loop in Excel

1 Answers1