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?

edit sheet name
 

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?

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?