Special Characters & Duplicate Names

[dChuck]

Hello,
I have a Macro that I am using to generate a weekly report which works great. The comments in the macro explains what it is that the macro does. This all works great until it runs up against special characters in the project name (as you know worksheet names cannot have special characters) or there is a duplicate name (meaning that the project name leads with the same 31 characters & are being used in the name of another worksheet.

What I’d like to improve the macro to now do is;

1. Strip all special characters that are not allowed to be used in a worksheet name, and
2. Pop up an input box when it comes up against a duplicate name that shows the project’s name and allows me to enter an alternate name.
3. Have an Input box that would allow me to select specific project names to run the macro for or to run it for all of them (think this is just a wish and not as important as the first two – Think I can get this once I have A & B fixed)

Problem is that I just can’t seem to find how to do this in the macro… Macro is below and appreciate any help I can get.
Thanks

Sub OPR_AutoGen()
'This Macro performs the following;
' 1. Unprotects Sheet (1) (Report Template) and changes the data source to the current worksheet
' 2. Creates a copy of the Report Template for each of the projects listed in the Program Status Report
' 2. Enters the project name in Cell “A1” for the specific project report created
' 3. Renames the individual project worksheet an abbreviated name (worksheet names are limited to 31 characters)
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Program Status Summary").Range("A2")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

Sheets(1).Select 'Select One-Page Template
ActiveSheet.Unprotect ' Unprotects it
Cells.Replace What:= _
"[2016-07-21 OT Program Status reporting - original TEMPLATE from TIMO.xls]", _
Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
False, SearchFormat:=False, ReplaceFormat:=False 'Changes the Data Source to the current Workbook

For Each MyCell In MyRange

Sheets(1).Copy After:=Sheets(Sheets.Count) 'Create a new worksheet as a copy of the one-page report template
'Sheets(Sheets.Count).Select ' ensure the created sheet is selected
Range("A1").Select ' Select cell A1 (Project Name input area)
ActiveCell.FormulaR1C1 = MyCell.Value ' Enters the full name of the project into the cell
Range("CJ1").Select 'Move to Cell (Abbreviated name area)
ActiveCell.FormulaR1C1 = "=LEFT(RC[-87],30)" 'Enter the first 20 characters of the project name located in A1 into the cell (Note worksheet name limited to 31 characters)
ActiveSheet.Name = Range("CJ1").Value 'Name the active worksheet the abbreviated project name in G1

Next MyCell

End Sub

 

[oldbrewer]

why not write a new macro that searches for ANY special character in any cell and changes them to "Z"

if there is a max of 3 sp chars in any cell just run the macro 3 times

or just use find and replace manually

 

[dChuck]

why not write a new macro that searches for ANY special character in any cell and changes them to "Z"

if there is a max of 3 sp chars in any cell just run the macro 3 times

or just use find and replace manually

Could, run that manually but what would be the logic in running it manually and then the creation part on a 2nd macro.

Given that I have between 25 - 35 projects that would simply take a bit of time.

Would really like to see how I could automate.

 

[JLGWhiz]

I believe it is the file names that have the special character restriction, not the sheets. Give this modified sheet naming snippet a try and see if it will work when you have an existing sheet name.

On Error Resume Next
ActiveSheet.Name = Range("CJ1").Value 'Name the active worksheet the abbreviated project name in G1
    If Err.Number > 0 Then
        ActiveSheet.Name = "Mod" & Left(Range("CJ1").Value, 27) 'Name the active worksheet the abbreviated project name in G1
    End If
On Error GoTo 0