-1

I have a VBA enabled excel workbook which has more than 20 sheets. Now the code in the VBA is using Worksheets numbers to call the worksheets.

Now I will have to delete one sheet which makes all the code unusable because if I delete one sheet the worksheet number changes.

Example Worksheets(10).Activate will point to another worksheet since I will be deleting the third worksheet

I am fairly new to this, so what would be the fastest way instead of manually changing the worksheet number in the code.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Parijat
  • 11
  • 1
    Using these numbers, and selecting sheets is a bad place to start from. You could benefit from reading on https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luuklag Feb 11 '19 at 08:29
  • why not refer to worksheets by name? Do you not need to keep track of which page is which? If you simply need to renumber then For Each of the worksheets collection. – QHarr Feb 11 '19 at 08:30
  • Thank you everyone for your feedback. I know it is a bad approach, but this is how the existing code is written and unfortunately I will have to work on it so I was looking for an automated way if anyone is aware. – Parijat Feb 11 '19 at 09:08

1 Answers1

1

Note that there are 3 ways to access a sheet …

  1. by its position in the tab bar:
    Worksheets(10) gives the 10ᵗʰ worksheet in the tab bar. Note that if you also have eg chart sheets the numbering is different from Sheets(10) which then might be a completely different sheet.

  2. by its visible tab bar name:
    Worksheets("Sheet10")

  3. by its VBA name:
    Sheet10
    Note that the VBA name can only be changed in the Project window of the VBA-Editor.

Note that Worksheets(10), Worksheets("Sheet10") and Sheet10 can be completely different sheets, because they use 3 completey independ naming systems.

Which one to use highly depends on what you are actually trying to do. There is no best option in general each of them has their advantages and disadvantages.

Additionally I highly recommend not to use .Activate and .Select at all. Therefore read How to avoid using Select in Excel VBA.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73