Inserting a Formula into a cell with a sheet name

[TriGuy51]

Help! The follwoing code gives me an Automation error message, that I think is because the Sheet name contains an apostrophe. The sheet name is shown in []. Any help, like how to delete the offending characters and still have the correct formula reference will be greatly appreciated.

For ss = 1 To SheetCount 'SheetCount is the number of sheets
ll = ss + 1
zz5 = Row_A(ss)
Wkbk1.Worksheets("Summary").Cells(ll, 1).Value = SheetNames(ss)
fstring = SheetNames(ss) '[SIDE EXTENSION (1'-6")] - bad sheet name causing the error
fstring = Chr(61) & Chr(39) & fstring & Chr(39) & "!L" & zz5
Wkbk1.Worksheets("Summary").Cells(ll, 5).Formula = fstring
Next ss

 

[CharlesChuckieCharles]

From the 'Rename' help in excel

These characters cannot be used in a file name: greater-than or less-than signs (< >), asterisks ( * ), question marks ( ? ), double quotes ( " ), vertical bars or pipes ( | ), colons ( : ), forward slashes (/), or brackets ( [] ).



and single quotes ( ') leading not good...

In fact Try renaming (in 2007) and it won't allow invalid characters to be typed in

 

[AlphaFrog]

Your sheet names are surrounded by a single quote at the beginning and end of each name. To have a literal single quote in between the two surrounding single quotes, that literal single quote has to be represented by two adjacent single quotes within the string.

This replaces the one single quote with two adjacent single quotes.

    For ss = 1 To SheetCount    'SheetCount is the number of sheets
        ll = ss + 1
        zz5 = Row_A(ss)
        Wkbk1.Worksheets("Summary").Cells(ll, 1).Value = SheetNames(ss)
        fstring = SheetNames(ss)    [COLOR="Green"]'[SIDE EXTENSION (1'-6")] - bad sheet name causing the error[/COLOR]
        [COLOR="Red"]fstring = Replace(fstring, Chr(39), Chr(39) & Chr(39))[/COLOR]
        fstring = Chr(61) & Chr(39) & fstring & Chr(39) & "!L" & zz5
        Wkbk1.Worksheets("Summary").Cells(ll, 5).Formula = fstring
    Next ss

 

[TriGuy51]

Thantks to all. Alpha - your code worked like a charm!!