Numbered Excel Sheets Cause Macro Problems

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!
______________