VBA Tab creation

Question

I need to create multiple worksheets in a workbook as per values in coloumn A. The names of the worksheets should be as per the data in coumn A.

I have created a vba code for the same but I am not able to loop it after first cell to seond cell and so on. Below is the my code.

Sub inputboxandsheetname()

    Dim myValue As Variant

    Dim irow As Long

    irow = 1

    Do While Cells(irow, 1) <> Empty

        myValue = Cells(irow, 1)

        If myValue <> "" Then

            Sheets.Add After:=Sheets(ActiveWorkbook.Worksheets.Count)

            ActiveSheet.Name = myValue

        End If

        irow = irow + 1

    Loop

End Sub

possible duplicate of [VBA Excel macro: use Range to act on a different worksheet](http://stackoverflow.com/questions/2656443/vba-excel-macro-use-range-to-act-on-a-different-worksheet) — GSerg, Aug 14 '15 at 09:23

score 0 · Accepted Answer · answered Aug 14 '15 at 09:28

0

Cells(irow, 1) <> Empty
If you don't qualify the range with a sheet object, it will always refer to the ActiveSheet. When you add a new sheet, this becomes the active sheet which will contain blank cells.

Also, Empty is used in VBA to check if a variant variable has a value assigned to it, not to check if a range contains a value.

Try the following instead:

Sub SO()

Dim indexSheet As Excel.Worksheet
Dim i As Integer
Set indexSheet = ActiveSheet
i = 1

While indexSheet.Cells(i, 1).Value <> vbNullString
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = indexSheet.Cells(i, 1).Value
    i = i + 1
Wend

End Sub

answered Aug 14 '15 at 09:28

SierraOscar

17,507
6
40
68

`Cells(irow, 1) <> Empty` [is in fact](http://stackoverflow.com/q/20737979/11683) `Cells(irow, 1).Value <> Empty`. `Value` is `Variant`, so it is exactly the case of checking if a variant variable has a value. – GSerg Aug 14 '15 at 09:32
I know that checking in this way _will_ work - but I see a lot of cases where things like `Empty` and `IsEmpty()` are used incorrectly and cause confusion - and so for the case of checking a blank cell I try to advise people against this method unless they understand _why_ and _how_ it works. Just a personal preference. – SierraOscar Aug 14 '15 at 09:35
Thank you.. It worked fine as per my way. Next time I will make sure to add the points you have mentioned. – Striker Aug 14 '15 at 09:39

VBA Tab creation

1 Answers1