Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Sheets (Need to create 500 individual sheets in one workbook, pulling DATA | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Worksheet Functions | |||
How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook? | Excel Programming | |||
Macro for filter on protected workbook that works for all sheets, no matter what sheets are named? | Excel Programming | |||
Allocate Files to Sheets and Build a Master Sheet which Summarises All Sheets | Excel Programming |