Sheet Numbering

  • I'm wondering if this is the way things work and there's nothing to be done about it (but I doubt that).

    I have a workbook that I load data into from a csv file. The csv file is "divided" into regions, and I want each region's group of data to be loaded into a separate sheet. To be on the safe side, I delete all the sheets before loading the data with the following code that I found in this forum

    Then, for each new region, I create a new sheet with the following code

    1. On Error Resume Next
    2. sheet_nr = sheet_nr + 1
    3. Sheets(sheet_nr).Activate
    4. If Err.Number <> 0 Then
    5. ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
    6. End If
    7. On Error GoTo 0

    Trouble (?) is, what Im'seeing is that even though the code seems to be working inasmuch as I get my new sheets for each region, each time I run the code, the sheet numbers in my VBA project are being incremented. For example, assuming 4 regions, then the very first time, I get something like


    Sheet1 (Region1)
    Sheet2 (Region2)
    Sheet3 (Region3)
    Sheet4 (Region4)

    The next time I run it, I get


    Sheet4 (Region1)
    Sheet5 (Region2)
    Sheet6 (Region3)
    Sheet7 (Region4)

    etc etc.
    Does this matter ? Can I "correct" it to restart at Sheet1 ?

  • Re: Sheet Numbering

    "Sheet1", "Sheet2", ... are the code names of sheets in Excel, assigned sequentially when the workbook is opened. They will reset only when you delete all the extra sheets, and close and reopen the workbook.

    You can rename the sheets in the VB Properties window, but you have to careful to avoid a namespace collision. You can also probably do it in code, but I don't know how. Absent a compelling reason to do so, I don't think I'd bother to learn.


    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Sheet Numbering

    Thanks to both of you for your answers. Since I was rerunning my macro WITHOUT closing/reopening the workbook, I never noticed the fact that that would have renumbered them automatically.

    The datapig stuff was nice, but it's almost more fun for me to reinvent the wheel (and learn new stuff as I go along). ;)