I use the following macro to create a duplicate sheet.

Sheets("ICS 214").Select
Sheets("ICS 214").Copy Befo=Sheets(2)
'
End Sub

The problem is it copies the user inputted data from the first sheet rather
than giving me a blank section. How can I modify the macro so it will
automatically delete info in a range of cells such as d29-d46 on the newly
created sheet?

see examples on my sheet.htm page, of course you would want to do error
checking.

'Copy the active worksheet to just before the currently active sheet [copy sheet]
ActiveSheet.Copy Befo=ActiveSheet
'Copy the active worksheet to after the last worksheet
ActiveSheet.Copy After:=Sheets(Sheets.Count)'Rename current Sheet
ActiveSheet.Name = "Renamed14a"'delete constants, retaining formulas in the range d29-d46
Range(D29:D46).SpecialCells(xlConstants).ClearCont ents---HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]My
Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htmSearch Page: http://www.mvps.org/dmcritchie/excel/search.htm
"EAHRENS" wrote in message ...
I use the following macro to create a duplicate sheet.

Sheets("ICS 214").Select
Sheets("ICS 214").Copy Befo=Sheets(2)
'
End Sub

The problem is it copies the user inputted data from the first sheet rather
than giving me a blank section. How can I modify the macro so it will
automatically delete info in a range of cells such as

retaining formulas in the range d29-d46
Range(D29:D46).SpecialCells(xlConstants).ClearCont ents

I am sure I am doing something wrong. The macro I am running now reads:
Sub ICS214()
'
' ICS214 Macro
' Macro recorded 10/5/2005 by Eric Ahrens
'
'Copy the active worksheet to just before the currently active sheet [copy
sheet]
ActiveSheet.Copy Befo=ActiveSheet
'Copy the active worksheet to after the last worksheet
ActiveSheet.Copy After:=Sheets(Sheets.Count) 'Rename current Sheet
ActiveSheet.Name = "Renamed14a" 'delete constants, retaining formulas in
the range d29-d46
Range(D29:d46).SpecialCells(xlConstants).ClearCont ents


'
End Sub

When I run it, I get a syntax error message highlighting:

Range(D29:d46).SpecialCells(xlConstants).ClearCont ents

What am I doing wrong?

"David McRitchie" wrote:

retaining formulas in the range d29-d46
Range(D29:D46).SpecialCells(xlConstants).ClearCont ents

You need the range addresses within double quotes.
Range("D29:d46").
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"EAHRENS" wrote in message news:46FF8DDC-9AEA-43E2-BCB0-

Range(D29:d46).SpecialCells(xlConstants).ClearCont ents

You are a genius. Thanks for your patience. I would like to bother you for
one more thing. The macro will now create a second page as needed. If I
then try to create an additional (page 3 or more) copy, I get an error
message about duplicate names although the duplicates are created. The
application I am using this for can require numerous copies of these forms.
How can I get around the duplicate name message?

"David McRitchie" wrote:

You need the range addresses within double quotes.
Range("D29:d46").
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"EAHRENS" wrote in message news:46FF8DDC-9AEA-43E2-BCB0-

Range(D29:d46).SpecialCells(xlConstants).ClearCont ents