Hi Bob, I don't see the sheet codename; I may not be looking at the macro as
you have descibed. Here is the macro:

Sheets("Sheet1").Select
Sheets("Sheet1").Copy Befo=Sheets(1)
Range("E2:G2").Select
Selection.ClearContents
Range("K2").Select
Selection.ClearContents
Range("E7").Select
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("M7").Select
Selection.ClearContents
Range("E9").Select
Selection.ClearContents
Range("I9").Select
Selection.ClearContents
Range("M9").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("E20:F20").Select
Selection.ClearContents
Range("I20").Select
Selection.ClearContents
Range("G22:M22").Select
Selection.ClearContents
Range("D24:M24").Select
Selection.ClearContents
Range("S7").Select
Selection.ClearContents
Range("W7:X7").Select
Selection.ClearContents
Range("S9:AA9").Select
Selection.ClearContents
Range("Q11:AA12").Select
Selection.ClearContents
Range("S14").Select
Selection.ClearContents
Range("Y14").Select
Selection.ClearContents
Range("Q17:AA17").Select
Selection.ClearContents
Range("Q18:AA19").Select
Selection.ClearContents
Range("E2").Select
End Sub


"Bob Phillips" wrote:

Use the sheet codename.

If you look at the project window with a worksheet selected, you will see
there are two names, like Sheet1(Sheet1). the first is the codename, the
second is the Excel name. The codename remains even if the Excel name is
changed.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"MarkT" wrote in message
...
Hi Everyone,

I have a macro that I run on a 2007 workbook that makes a duplicate copy
of
the current sheet by hitting a button with the macro associated with it.
The
macro runs fine when the sheet is named "Sheet1"; however, once a
duplicate
sheet has been created, I need to change the name of that sheet, then when
I
go and hit my macro button again, I get an error since I have now renamed
"Sheet1" to something else.

In VB, is there a term that I may use to duplicate the current sheet
selected? I need to have a variable term used instead of the current
"sheet1" that is in my macro.

Thanks for your help as always!

Mark