folks,
I need to remove the title "Dr" from a list of names prior to publishing. Not all names have this title. I can find the substring within my specific range with
where nm1 contains a name. nm1 refers to the cell C9 on my spreadsheet.
if nm1 does not contain the substring "Dr ", pos will be zero. if it does contain the substring, pos will be greater than zero, so remove
I will then be saving the file using the string (now without Dr in it) as the name. Can i reuse the nm1 variable or do i need to create another?
so far i have
I need to remove the title "Dr" from a list of names prior to publishing. Not all names have this title. I can find the substring within my specific range with
Code:
pos = InStr(nm1, "Dr ")
if nm1 does not contain the substring "Dr ", pos will be zero. if it does contain the substring, pos will be greater than zero, so remove
Code:
If pos > 0 Then .... remove
I will then be saving the file using the string (now without Dr in it) as the name. Can i reuse the nm1 variable or do i need to create another?
so far i have
Code:
Sub SavePdf()
Dim ws As Worksheet
Dim nm1 As String
Dim nm2 As String
Dim pos As Integer
For Each ws In ActiveWorkbook.Worksheets
ws.Select
nm1 = Range("C9")
nm2 = Trim(Range("A2"))
'pos = InStr(nm1, "Dr ")
' If pos > 0 Then nm3 = Replace("Dr ", 7, "")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Temp\Rerun\" & nm1 & " CHQ " & nm2 & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Next ws
End Sub