Naming multiple sheets

A

Amanda

Hi,

I have the following code which adds lots of sheets to a workbook:

Sub AddSheets()

Dim iNoSheets As Integer

Dim x As Integer

iNoSheets = Application.InputBox("How many sheets to add?", "Add Sheets",
10, , , , 1)

For x = 1 To iNoSheets

Worksheets.Add

Next x

End Sub


How do I amend it so it changes the tab name from sheet1, sheet2 etc to a
name taken from a list of branches which are in 'sheet1' of the same file?????

Thanks

Amanda
 
J

John

You may need to amend some of the code to account for sheets already named
but if starting from scratch this is how i would do it in my total
non-professional capacity as a VBA'er!!

In Sheet1 name the range with the Branch names in, eg Tbl_branches


JB :)

Sub AddSheets()

Dim iNoSheets As Integer>
Dim x As Integer
Dim Tbl1 as range
Dim MyBranch$
Set Tbl1 = Tbl_Branches
iNoSheets = Application.InputBox("How many sheets to add?", "Add Sheets",
10, , , , 1)

For x = 1 To iNoSheets
Worksheets.Add
Sheets("sheet" & x).Name = Application.worksheetfunction.index(tbl1,x,1)

Next x

End Sub
 
A

Amanda

Hi John,

Thanks for this. I don't know VBA at all!

I've run this and it keep highlighting the row 'Set Tbl_...' as a problem.
Any ideas??

Thanks

Amanda
 
Top