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

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

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

"John" wrote:

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