Add n sheets with a "For cycle" and name them using the name ina range

scorpione

New Member
Joined
Apr 23, 2013
Messages
6
Hallo guys,

-Windows XP
-Excel 2010

I need to add 3 new spreadsheets giving them a name taken from a range in another sheet. I used the code below, but after that excel creates the first new spreadsheet with the correct name, it creates the second sheet with the generic name (ex. Sheet17) and then I got the following error:

Run-time error ‘1004’
Application-defined or object-defined error.


Sub NewSheets()
For i = 2 To 4
Worksheets.Add().Name = Worksheets(14).Cells(i, 8).Value
Next i
End Sub

In general I would need to create n sheets giving them the name taken from a range made of n cells. n is variable. So I thought that the cycle “For” would fit my needs. If you think that there is a better way, please let me know!

Thanks

Scorpione
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
Office Version
  1. 365
Platform
  1. Windows
What is name of your worksheet 14 ?

PUT THAT NAME INSTEAD OF 14
 
Last edited:

scorpione

New Member
Joined
Apr 23, 2013
Messages
6
Hi Kevatarvind,

the name of worksheet 14 i "Broker_VBA"

Ciao
Scorpione


It works!!! Thanks a lot.
But I don't understand why it's not working with only the index.
 
Last edited:

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
Office Version
  1. 365
Platform
  1. Windows
try below
Code:
Sub NewSheets()
Sheets(1).Select
For I = 2 To 4
Worksheets.Add().Name = Worksheets("Broker_VBA").Cells(I, 8).Value
Next I
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,932
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I believe your question has been answered but in case you always want to use the 14th sheet but it might not always have the same name, you could do this.
Code:
Sub NewSheets2()
  With Worksheets(14)
    For i = 2 To 4
      Worksheets.Add().Name = .Cells(i, 8).Value
    Next i
  End With
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,932
Office Version
  1. 365
Platform
  1. Windows
I don't understand why it's not working with only the index.
The reason is (most likely):
When your code adds a new sheet, it adds the new sheet immediately to the left of the active sheet. So, if the active sheet is the 14th sheet or any to the left of that, your original 14th sheet will now be the 15th sheet for the second time through the loop. Most likely Cells(2,8) on what is now the 14th sheet is empty and the error is because your code is trying to name a sheet with a null string, which of course is disallowed and hence the error.
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

bcoz when first sheet added then 14 no sheet becomes 15 thats why its giving error
 

scorpione

New Member
Joined
Apr 23, 2013
Messages
6
Hi Peter,

Thanks for your answer. I'm quite new using VBA, so I don't know if in this case is better to use the name of the index, but in general I would say that it is easy to operate and manipulate numbers than string.

What do you think?

Ciao
Scorpione
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,932
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,

Thanks for your answer. I'm quite new using VBA, so I don't know if in this case is better to use the name of the index, but in general I would say that it is easy to operate and manipulate numbers than string.

What do you think?

Ciao
Scorpione
Using index numbers is risky. You never know when the sheets might be rearranged (or when somebody might insert new sheets like happened here :))
If you know the name of the sheet, I would generally use that. It's not fool-proof either though as the sheet could get re-named.
At least reading code with the sheet name in it, it is clear what sheet is intended to be used.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,651
Messages
5,854,958
Members
431,689
Latest member
jacker01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top