Inserting a Formula into a cell with a sheet name

TriGuy51

New Member
Joined
Mar 23, 2010
Messages
25
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Last edited:
Upvote 0
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.

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

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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