help

[dd192193]

record a macro wich delete data from several sheets those sheets names change all the time how can i make it happen that when i change the sheet name it will also be changed in the visual basic code at this time i got the error message runtime error'9': subschript out of range .
working with one workbook and about 20 sheets with different names and contiquous changing .(excel)

danny

 

[JeffTullin]

Can't understand why you keep changing the sheet names - that must get confusing??
Anyway, assuming that the sheets only change names, but do not get created or destroyed, then you can refer to the sheets by ordinal number instead of name.
The first sheet is Sheets(1), the next is Sheets(2) and so on.
This remains true even if the names change.

Your macro then looks like this:

Sheets(2).Select
Range("A110&quot.Select
Selection.Delete Shift:=xlToLeft

instead of
Sheets("Randomname&quot.Select
...


For clarity, you may wish to set up some constants to refer to the sheets.
Example: The first sheet always holds ages; it may have begun life called 'Ages' but now is called 'Some Ages'

Declaring constants in the VBA modules such as:

Public Const SHEET_AGES =1
Public Const SHEET_NAMES =2

etc

will allow you to rework the macros with meaningful values, like this:


Sheets(SHEET_NAMES).Select
Range("A110&quot.Select
Selection.Delete Shift:=xlToLeft