Sheet Names In Macro

[Paul Moles]

I am trying to write a Macro to copy part of one sheet to
another, simple enough, but if the name of the source
sheet is changed the macro produces a run time error.
Is there a way of sourcing the new name and updating the
macro before the copy process section of the macro starts
or is there some constant "internal" name for each sheet?

 

[Bob Phillips]

--
HTH

 

[Bob Phillips]

Paul,

You could use the worksheet code name, which is less volatile.

When a worksheet is created it has 2 names the worksheet name, and the code
name. Initially, these are the same (go into the VB IDE and you will see
Sheet1(Sheet1) alongside the worksheets. As you have found the user can
easily change the sheet name, and then in the VB IDE you will see
Sheet1(Bob) say. But note the original Sheet1 is unaltered.

To access sheets via code name you simply use
Sheet1.Range("A1")
rather than
Worksheets("Sheet1").Range("A1")

 

[David McRitchie]

Hi Peter,
This is really a programming question, but since you know about
macros at least I don't have to tell you how to install one.

Sub AddNewSheet_example()
Dim xlSheet As Worksheet
Set xlSheet = ActiveWorkbook.Sheets.Add
xlSheet.Name = "My New Worksheet"
'-ooo your code here ooo-

Set xlSheet = Nothing '-- release when done
End Sub

More information on the above
http://www.mvps.org/dmcritchie/excel/sheets.htm#newsheets
An example:
http://www.mvps.org/dmcritchie/excel/code/bus_sched.txt