string extraction not working.

  • Thread starter Thread starter SteveDB1
  • Start date Start date
S

SteveDB1

Hi all.
I'm attempting to create a worksheet name extraction tool and have obtained
the following.
Code-------------------------------------------------------------------------------

Function stGetFileName() As Variant
Dim nwShtNm As Worksheet
Dim nwShtNm1 As String

stGetFileName() = ThisWorkbook.Name

'need code to ensure that a worksheet gets named correctly.
'what I have here is not it.
nwShtNm1 = stGetFileName()

If Worksheet.Name = "Sum" Or "SUM" Then
nwShtNm.Name = "Sum-" & nwShtNm1

ElseIf Worksheet.Name = "Summary" Then
nwShtNm = "Sum-" & nwShtNm1

ElseIf Worksheet.Name = "APN" Then
nwShtNm = "APN-" & nwShtNm1

End If
End Function

----------------------------------------------------------------------------------------------

This is not working as hoped.
I'm activating a workbook, looking for two worksheets-- Sum, or Summary, or
SUM, and APN.
I then want to rename those two worksheets from the book's name-- generally
a 7 character string starting with "DTR-XXX" as shown above.
I'm wanting to extract the last 3 digits- xxx.
I've tried variations of the above, and this is as close as I've gotten thus
far.
the function just bounces back and forth between the function's name, and the
stGetFileName() = ThisWorkbook.Name line.
 
Steve this is what I would do. This code will scan all the worksheets in the
active workbook. If the active workbook has a worksheet named "SUM", "Sum",
"Summary", or "APN" then it changes that worksheets name by adding the last
three characters of the active workbooks name.

Option Explicit

Sub RenameWorksheets()

Dim strWbkName As String
Dim wks As Worksheet

' get last 3 characters of the workbook name
strWbkName = Right(ActiveWorkbook.Name, 3)

' find worksheet name and change worksheet name if found
For Each wks In ActiveWorkbook.Worksheets
Select Case wks.Name
Case "Sum", "Summary", "SUM"
wks.Name = "Sum-" & strWbkName

Case "APN"
wks.Name = "APN-" & strWbkName
End Select
Next wks

End Sub

Hope this helps! If so please click "Yes" below or reply.
 
Ryan,
Thank you for your help.
I have one last element of this that I need help for.
It does indeed grab the last 3 characters of the filename, but those appear
to be the file extension.
what would I use to strip off the file extension?
Again-- thank you!
 
Replace strWbkName = Right(ActiveWorkbook.Name, 3) with this:

Dim myArray As Variant
Dim strWbkName As String

' break apart workbook name where there is a period
myArray = Split(ActiveWorkbook.Name, ".")

' return last 3 characters or first portion of workbook name
strWbkName = Right(LBound(myArray), 3)
 
great.
Thank you again.
Best.
SteveB

RyanH said:
Replace strWbkName = Right(ActiveWorkbook.Name, 3) with this:

Dim myArray As Variant
Dim strWbkName As String

' break apart workbook name where there is a period
myArray = Split(ActiveWorkbook.Name, ".")

' return last 3 characters or first portion of workbook name
strWbkName = Right(LBound(myArray), 3)
Click to expand...
 
Back
Top