Originally Posted by
vbaNewbieMan
I didn't realize that Chr(34) was used only for sheets with quotes (which I do have in this case). The code will reside in the personal workbook so that someone can open a new updated version of the workbook they receive, so I think I will need to reference each sheet with Sheets(Sheet1). In my case though, the sheet being referenced will be "P" (with the quotes) so I'm assuming that I would need the Chr(34) in order to reference them correctly.
I think you have some confusion about how quotes work when referring to sheet names. Let me see if I can explain this.
By default when you open Excel, you will see a sheet named
Sheet1
That is what I assume you have. To refer to this sheet in your code, you can use a string containing the sheet name as an index in the collection called Worksheets (or Sheets). To make a string, simply enclose the sheet name in double quotes. This is done as in the code I provided:
The sheet name is enclosed in quotes. The string returned is
Sheet1
same as what the tab shows.
The character Chr(34) is the double quote character ("). When you add that, you are adding quotes to the actual sheet name. It is certainly possible to create a sheet name with quotes, but I would be surprised if that's what you intend. You can edit a sheet name to see this on the tab:
"Sheet1"
If you do have a sheet name like this, then you would need to modify the code to look like your original code, or alternatively this:
In VBA, to show a double quote inside a quoted string you can repeat the quote twice in succession (or concatenate Chr(34) as you have done).
Bookmarks