Numbered Excel Sheets Cause 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.

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.

FilterRep01

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.

FilterRep02

Sure enough, the macro created all the sheets, without problems, the first time it ran.

FilterRep03

Then, as promised, I got an error the second time that it ran.

FilterRep04

And mysteriously, the Data sheet had been cleared.

FilterRep05

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.

FilterRep06

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)) and instead of looking for the sheet with an index number of 1, it looked for a sheet with a name of “1”.

FilterRep07

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!

______________

You may also like...

4 Responses

  1. greg kramer says:

    nice tip…Thanks!

  2. Kanti Chiba says:

    Thanks, you have just alerted me to a very real potential problem as I have sheets that are made up of ddmmyy, 161109

  3. sam says:

    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

  4. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *