Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
edit sheet name
Background
I'm using EXCEL to convert electrical data formats and perform analysis. We do this in steps and like to include the labels RAW and PREP ant the end of the worksheet name (tab). We protect those sheets and make copies of the them for analysis so we don't screw up the raw and prepared data. I am trying to automate the data preparation process and am having some trouble with tab naming. Problem To prep the raw data we use Public Sub ProtectRaw() Dim oldName 'copy original sheet name oldName = ActiveSheet.Name 'add RAW to the end of the worksheet name ActiveSheet.Name = oldName & "_RAW" 'Protect worksheet ActiveSheet.Protect End Sub Works fine This code makes a new sheet, prepares the data, protects it and adds PREP to the sheet name: Public Sub PrepData() Dim sourceName 'copy original sheet name sourceName = ActiveSheet.Name 'create new workesheet ActiveSheet.Copy Befo=ActiveSheet 'UnProtect new worksheet ActiveSheet.Unprotect 'Run data prep procedure ConvPMFLTS1_Main 'modify the new worksheet name ActiveSheet.Name = sourceName & "_PREP" 'UnProtect new worksheet ActiveSheet.protect End Sub Everything works except the sheet name is "sheetname _RAW_PREP" and I need to remove "_RAW" from the name at the end of my code. How do I do that? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
edit sheet name
Worked perfect Jason
Thanks! Jason Lepack wrote: Instead of : oldname = activesheet.name use: oldName = Left(ActiveSheet.Name, Len(ActiveSheet.Name) - 5) This will take off the last 5 characters, or the "_PREP". Cheers, Jason Lepack wrote: Background I'm using EXCEL to convert electrical data formats and perform analysis. We do this in steps and like to include the labels RAW and PREP ant the end of the worksheet name (tab). We protect those sheets and make copies of the them for analysis so we don't screw up the raw and prepared data. I am trying to automate the data preparation process and am having some trouble with tab naming. Problem To prep the raw data we use Public Sub ProtectRaw() Dim oldName 'copy original sheet name oldName = ActiveSheet.Name 'add RAW to the end of the worksheet name ActiveSheet.Name = oldName & "_RAW" 'Protect worksheet ActiveSheet.Protect End Sub Works fine This code makes a new sheet, prepares the data, protects it and adds PREP to the sheet name: Public Sub PrepData() Dim sourceName 'copy original sheet name sourceName = ActiveSheet.Name 'create new workesheet ActiveSheet.Copy Befo=ActiveSheet 'UnProtect new worksheet ActiveSheet.Unprotect 'Run data prep procedure ConvPMFLTS1_Main 'modify the new worksheet name ActiveSheet.Name = sourceName & "_PREP" 'UnProtect new worksheet ActiveSheet.protect End Sub Everything works except the sheet name is "sheetname _RAW_PREP" and I need to remove "_RAW" from the name at the end of my code. How do I do that? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet Protection / Edit Objects | Excel Worksheet Functions | |||
Edit links when the sheet is protected | Excel Discussion (Misc queries) | |||
How do view and edit ALL hyperlinks in a sheet? | Excel Discussion (Misc queries) | |||
Selct and edit sheet | Excel Discussion (Misc queries) | |||
Using Ref edit in Add in to save to a different sheet. | Excel Programming |