Need to find and replace text inside a macro using vba

[filet2050]

My employer is using an excel 2000 workbook with 30 worksheets. Employee
names are on the tabs at the bottom. The sheets are printed at the end of
the month and given to each employee.

I recorded a macro that I use to clear the data (hours worked, etc.) on all
of the worksheets when a new month starts. My employer used to clear the
contents on each sheet manually.

I also have a macro that I wrote using vba (after considerable trial and
error) that changes a name on the tab on the selected worksheet using an
input box (when a new employee is added or subtracted). If the employee
count goes down I am using a place holder (Z1 through Z9) so that it sorts
toward the end of the sheets. The macro then resorts the sheets
alphabetically.

This macro doesn't update the macro that clears the data for a new month.
When a name tab is changed I have to manually do a find and replace on the
new month macro to update the sheet name or the new month macro will freeze,
because it can't find the name after it is removed. It also needs any new
names added or it won't clear the data on the new employee(s) the following
month.

I would like to write a program in vba that ties in with the macro that
updates the tab name, to look for the old name in the monthly update macro
and replace it with the new name. That way the new month macro will run
without hanging up.

I have very little experience working with vba code and I can't find
anything that seems to work. This is my first posting. Any help will be
greatly appreciated.

 

[Joel]

I would use a worksheet "NAME" for each tab. Excel will change the reference
for NAME when the tab is modified.

1) Go to worksheet menu Insert-Name-Define
add name JOEL for cell A1 - This will include the sheet name as well

2) Use this code to clear contenets of worksheet
cellname = Range("Joel").Worksheet.Name
Sheets(cellname).Cells.ClearContents

When the Tab name is changed the reference for JOEL will also change. The
VBA code will not have to be modified.

 

[filet2050]

Thak you Joel. It worked perfectly.