Help needed with Macro
 

I have a Workbook with 13 sheets, the first is a Control sheet. On the
control sheet I have a button (Macro details below) which when clicked
should rename the other 12 sheets according to data in cells A4:A15

Sub namesheets_as_per_list_on_worksheet()
Dim arr As Variant
arr = Range("a4:a15").Value
For i = LBound(arr) To UBound(arr)
Sheets(i + 1).Activate
Sheets(i).Name = arr(i, 1)
Next i
End Sub

The conrtrol sheet is the first sheet in the workbook, and gets renamed
when I run the macro, whilst sheet 13 does not change. What changes does
the Macro require so that it renames sheets 2-13, ignoring sheet 1 the
control sheet


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=398905

Help needed with Macro
 

you almost had it!!!
you only have to change:

Sheets(i).Name = arr(i, 1)

to

Sheets(*i + 1*).Name = arr(i, 1)

as you did with the previous line...

just a note... you don't have to activate the sheet to change its
name...
at least, that is what happens with my office (2003)....

:)

Paul Sheppard Wrote:
I have a Workbook with 13 sheets, the first is a Control sheet. On the
control sheet I have a button (Macro details below) which when clicked
should rename the other 12 sheets according to data in cells A4:A15

Sub namesheets_as_per_list_on_worksheet()
Dim arr As Variant
arr = Range("a4:a15").Value
For i = LBound(arr) To UBound(arr)
Sheets(i + 1).Activate
Sheets(i).Name = arr(i, 1)
Next i
End Sub

The conrtrol sheet is the first sheet in the workbook, and gets renamed
when I run the macro, whilst sheet 13 does not change. What changes does
the Macro require so that it renames sheets 2-13, ignoring sheet 1 the
control sheet


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=398905

Help needed with Macro
 

Hi Paul

This is happening because by default array indexes start at 0. You can
change this to 1 by adding the statement "Option Base 1" to your module but
you may be better off trying it like this:

Sub namesheets_as_per_list_on_worksheet()
Dim i As Integer
For i = 2 To 13
Sheets(i).Name = Cells(i + 2, 1).Value
Next i
End Sub

Hope this helps
Rowan
"Paul Sheppard" wrote:


I have a Workbook with 13 sheets, the first is a Control sheet. On the
control sheet I have a button (Macro details below) which when clicked
should rename the other 12 sheets according to data in cells A4:A15

Sub namesheets_as_per_list_on_worksheet()
Dim arr As Variant
arr = Range("a4:a15").Value
For i = LBound(arr) To UBound(arr)
Sheets(i + 1).Activate
Sheets(i).Name = arr(i, 1)
Next i
End Sub

The conrtrol sheet is the first sheet in the workbook, and gets renamed
when I run the macro, whilst sheet 13 does not change. What changes does
the Macro require so that it renames sheets 2-13, ignoring sheet 1 the
control sheet


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=398905

Help needed with Macro
 

T-®ex Wrote:
you almost had it!!!
you only have to change:

Sheets(i).Name = arr(i, 1)

to

Sheets(*i + 1*).Name = arr(i, 1)

as you did with the previous line...

just a note... you don't have to activate the sheet to change its
name...
at least, that is what happens with my office (2003)....

:)

Thanks T-®ex, much appreciated


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=398905