how to use VBA to name sheets ?
 

I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help

--
vumia
-----------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...fo&userid=3649
View this thread: http://www.excelforum.com/showthread.php?threadid=56478

how to use VBA to name sheets ?
 

Sub Renamesheets()
dim sh as Worksheet
for each sh in workbook.Worksheets
sh.name = sh.Range("C4").Value
Next
end Sub


would be my guess of what you want

--
Regards,
Tom Ogilvy


"vumian" wrote:


I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help.


--
vumian
------------------------------------------------------------------------
vumian's Profile: http://www.excelforum.com/member.php...o&userid=36494
View this thread: http://www.excelforum.com/showthread...hreadid=564787

how to use VBA to name sheets ?
 

If you want to add the value in C4 from each sheet to the existing sheet
name you could do like this:

Sub Change_name()

Dim i As Integer

For i = Sheets.Count To 1 Step -1

Application.ScreenUpdating = False

Sheets(i).Activate

Sheets(i).Name = Sheets(i).Name & Range("C4").Value

Next i

Application.ScreenUpdating = True

End Sub


"Tom Ogilvy" wrote in message
...
Sub Renamesheets()
dim sh as Worksheet
for each sh in workbook.Worksheets
sh.name = sh.Range("C4").Value
Next
end Sub


would be my guess of what you want

--
Regards,
Tom Ogilvy


"vumian" wrote:


I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help.


--
vumian
------------------------------------------------------------------------
vumian's Profile:
http://www.excelforum.com/member.php...o&userid=36494
View this thread:
http://www.excelforum.com/showthread...hreadid=564787

how to use VBA to name sheets ?
 

or to make it faster

Sub Change_name()
Dim sh as Worksheet
For each sh in Worksheets
sh.Name = sh.Name & sh.Range("C4").Value
Next i
End Sub

Almost always better to avoid selecting and activating which are usually not
necessary

--
Regards,
Tom Ogilvy


"Alf Bryn" wrote:

If you want to add the value in C4 from each sheet to the existing sheet
name you could do like this:

Sub Change_name()

Dim i As Integer

For i = Sheets.Count To 1 Step -1

Application.ScreenUpdating = False

Sheets(i).Activate

Sheets(i).Name = Sheets(i).Name & Range("C4").Value

Next i

Application.ScreenUpdating = True

End Sub


"Tom Ogilvy" wrote in message
...
Sub Renamesheets()
dim sh as Worksheet
for each sh in workbook.Worksheets
sh.name = sh.Range("C4").Value
Next
end Sub


would be my guess of what you want

--
Regards,
Tom Ogilvy


"vumian" wrote:


I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help.


--
vumian
------------------------------------------------------------------------
vumian's Profile:
http://www.excelforum.com/member.php...o&userid=36494
View this thread:
http://www.excelforum.com/showthread...hreadid=564787

how to use VBA to name sheets ?
 

Small typo in your macro Tom

Line "Next i" should be "Next sh"
But your code is more efficent.

I tried first:

Sheets(i).Name = Sheets(i).Name & Range("C4").Value
But that only added the "C4" value from the sheet that is active when a run
the macro.

Your "sh.Name = sh.name & sh.Range("C4").Value" is a verry nice solution
making activating
the sheet not nececearry. I bow to your superior knowledge.

"Tom Ogilvy" wrote in message
...
or to make it faster

Sub Change_name()
Dim sh as Worksheet
For each sh in Worksheets
sh.Name = sh.Name & sh.Range("C4").Value
Next i
End Sub

Almost always better to avoid selecting and activating which are usually
not
necessary

--
Regards,
Tom Ogilvy


"Alf Bryn" wrote:

If you want to add the value in C4 from each sheet to the existing sheet
name you could do like this:

Sub Change_name()

Dim i As Integer

For i = Sheets.Count To 1 Step -1

Application.ScreenUpdating = False

Sheets(i).Activate

Sheets(i).Name = Sheets(i).Name & Range("C4").Value

Next i

Application.ScreenUpdating = True

End Sub


"Tom Ogilvy" wrote in message
...
Sub Renamesheets()
dim sh as Worksheet
for each sh in workbook.Worksheets
sh.name = sh.Range("C4").Value
Next
end Sub


would be my guess of what you want

--
Regards,
Tom Ogilvy


"vumian" wrote:


I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help.


--
vumian
------------------------------------------------------------------------
vumian's Profile:
http://www.excelforum.com/member.php...o&userid=36494
View this thread:
http://www.excelforum.com/showthread...hreadid=564787

how to use VBA to name sheets ?
 

thanks for the correction - I was editing your macro and didn't get the NEXT
cleaned up after I switched the loop to a For Each.

--
Regards,
Tom Ogilvy


"Alf Bryn" wrote:

Small typo in your macro Tom

Line "Next i" should be "Next sh"
But your code is more efficent.

I tried first:

Sheets(i).Name = Sheets(i).Name & Range("C4").Value
But that only added the "C4" value from the sheet that is active when a run
the macro.

Your "sh.Name = sh.name & sh.Range("C4").Value" is a verry nice solution
making activating
the sheet not nececearry. I bow to your superior knowledge.

"Tom Ogilvy" wrote in message
...
or to make it faster

Sub Change_name()
Dim sh as Worksheet
For each sh in Worksheets
sh.Name = sh.Name & sh.Range("C4").Value
Next i
End Sub

Almost always better to avoid selecting and activating which are usually
not
necessary

--
Regards,
Tom Ogilvy


"Alf Bryn" wrote:

If you want to add the value in C4 from each sheet to the existing sheet
name you could do like this:

Sub Change_name()

Dim i As Integer

For i = Sheets.Count To 1 Step -1

Application.ScreenUpdating = False

Sheets(i).Activate

Sheets(i).Name = Sheets(i).Name & Range("C4").Value

Next i

Application.ScreenUpdating = True

End Sub


"Tom Ogilvy" wrote in message
...
Sub Renamesheets()
dim sh as Worksheet
for each sh in workbook.Worksheets
sh.name = sh.Range("C4").Value
Next
end Sub


would be my guess of what you want

--
Regards,
Tom Ogilvy


"vumian" wrote:


I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help.


--
vumian
------------------------------------------------------------------------
vumian's Profile:
http://www.excelforum.com/member.php...o&userid=36494
View this thread:
http://www.excelforum.com/showthread...hreadid=564787