Naming Excel sheets – how hard can it be? Last month, we looked at sheet names, and saw the problems that were caused by using an apostrophe. This week, I found another sheet name problem.
Macro Creates Sales Rep Sheets
Someone, let’s call him Mr. X, contacted me about one of my sample worksheets — Create New Sheets from Filtered List.
The file has a macro that creates individual sheets for each sales rep, filtering data from a master sheet, like the one shown below.
Second Time Around
Mr. X said he could run the macro once, then got an error the next time he tried to run it. His email also included those dreaded words, “I made some changes.”
Unfortunately, there was no clue as to what those changes were, but at least he attached the problem file.
Instead of Rep names, he wanted to created a worksheet for each customer ID.
Sheets with Number Names
Sure enough, the macro created all the sheets, without problems, the first time it ran.
Then, as promised, I got an error the second time that it ran.
And mysteriously, the Data sheet had been cleared.
The Numbers Game
What happened? Well, the code runs though the list of Customer ID numbers, and if a sheet doesn’t exist for that number, it creates one.
If the sheet does exist, it clears the sheet, then adds the new data.
When the code got to customer ID 1, instead of clearing the “1” sheet, it cleared the first sheet, Data, which has an index number of 1.
To fix the problem, I changed the c.Value reference in the code to CStr(c.Value)).
Now, instead of looking for the sheet with an index number of 1, it looked for a sheet with a name of “1”.
Watch the Numbers
If you use numbers as sheet names, such as a sheet for each day of the month, be careful how you refer to those sheets in your code.
You don’t want to end up like Mr. X, with blank sheets, and error messages!
______________
nice tip…Thanks!
Thanks, you have just alerted me to a very real potential problem as I have sheets that are made up of ddmmyy, 161109
Never use Sheets(“Something”).Something
Also refer to the sheet by the Code name of the sheet.
So Sheet10.Cells.Clear will ensure that your client can change the name of the sheet without effecting the cod
Thanks Greg and Kanti, I hope the tip helps.
Sam, you’re right that code names can prevent problems in many cases. In this workbook though, the sheets are created on the fly. Then the sheet names are checked against a list of customer numbers on the worksheet.