Special Characters & Duplicate Names

dChuck

New Member
Joined
Sep 18, 2016
Messages
2
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top