VB code for add new worksheet with new row

September 29, 2011 at 06:09:37
Specs: Windows XP
Hi, there,
I am new to site as well as withVBA coding.Thought some genius from this site will rescue me.
I need to add new worksheets in excel workbook basd on names in column C. As a step-1,I have data names in column C from C2: C10,
I made this vba code to add and rename new work sheet based on data from C2:C10 with out any problems.
Sub CreateSheetsFromAList()
Dim MyCell As Range, MyRange As Range
Set MyRange = Sheets("ADD NEW CMPDS").Range("C2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub

The problem starts after this, when I enter new name in the same column C11, it doesnt generate new worksheet but rather throws an error.
Can some one help what code do I need so that every time add new row in column C it picks up that name from new row and generate new woksheet.

Apprecite your help and time.

See More: VB code for add new worksheet with new row

Report •

September 29, 2011 at 07:05:41
Hello, and welcome to the site.

Here is a quick example i have written, works ok for me, try it let us know if it works for you.

Dim BCell, URange, LRange As Range

Sub CreateSheets()
    Set URange = Range("C2")
    Set LRange = Sheets("Sheet1").Range(ActiveCell.Address)
    For Each BCell In Range(URange, LRange)
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = BCell.Value
    Next BCell

End Sub

Report •

September 29, 2011 at 08:30:17
2 comments on this line of your code:


Range("C65536") is the "old school" syntax from Excel versions prior to 2007.

Since 2007 and beyond can have more than 65536 rows, the following syntax should be used for all versions, making the code a bit more portable:

Range("C" & Rows.Count)

You are also Selecting a cell with that line.

Rarely do you have to Select an object in VBA, and doing so makes the code inefficient. It is more efficient to perform your actions on the object within VBA itself instead of have Excel Select the object first.

The same goal can be accomplished as follows:

Set LRange = Sheets("Sheet1").Range(Range("C" & Rows.Count).End(xlUp).Address)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

September 30, 2011 at 01:54:43
To both

AlwaysWilling ToLearn and DerbyDad03

Thanks guys for your reply.

I used the code what you worte, but still I am ending up with same issue
It adds worksheet properly when there is existing names in three rows in column C of the reference sheet.At this stage when I run the code, it works fine. After creating this new sheets, say if I add new name in 4th row Column C (reference sheet) and run the code again its throwing error again.

This is what I get

"Run-time error '1004'
Cannot rename a sheet to the samename as another sheet, a referenced object library or a workbook referenced by Visual Basic."

Any suggestions


Report •

Related Solutions

September 30, 2011 at 16:17:32
So what u r saying is, you run the code and it creates the new sheets, but when u add another name and re run the code it throws the error? Example

C1 = "test"
C2 = "test2"
C3 = "test3"

You run code and it creates the sheets
You now add

C4 = "test4"

You re run the code and it throws the error?

Well if thats the case then the reason this happens is because you cannot create two sheets with the same name. I'll modify this code to handle this exception.

@ DerbyDad, thanks mate, unfortunately old habbits die hard, but obviously you are right thanks for the corrections :)

Report •

October 1, 2011 at 05:17:26
Thanks mate, ya you got it right , that is where the problem is. I will wait for your modified code then :)


Report •

October 3, 2011 at 02:36:17
I couldnt find a better way of doing this, maybe DerbyDad can assist, however here is my attempt.
What i have done is, recorded in Range "K1" the row where the code ends, so basically if you have
C1 = A
C2 = B
C3 = C
C4 = D

Then K1 will become 5.

Then when you run the code again, it will start from C5 and ignore C1 to C4. This way it eliminates the
problem of creating sheets with the same name.

Before you run this code, make sure you put the number 2 in cell K1

Hope this helps

Dim BCell, URange, LRange As Range
Dim nCount

Sub CreateSheets()
    nCount = Range("K1").Value
    Set URange = Range("C" & nCount)
    Set LRange = Sheets("Sheet1").Range(Range("C" & Rows.Count).End(xlUp).Address)
    For Each BCell In Range(URange, LRange)
        Sheets.Add After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = BCell.Value
    Next BCell
    sheet1.Range("K1").Value = LRange.Row + 1
End Sub

Report •

Ask Question