edit sheet name

[robert.hatcher]

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 Before:=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?

 

[Jason Lepack]

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

 

[robert.hatcher]

Worked perfect Jason
Thanks!

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