Thanks, That helped and got rid of the error message. I thought I was done
and got a new problem. The sheet I am trying to copy is protected. The
range I want to delete is not. The macro won't allow the deletion to occur
if the sheet is protected at all. Is there a way around this? Is is
possible to have the macro turn off the protection, copy and delete and then
turn itself back on? Is there a better way? I really appreciate your help
on this. If it helps, I pasted what I have currently entered below.
Sub ICS214()
'
' ICS214 Macro
' Macro recorded 10/5/2005 by Eric Ahrens
'Copy the active worksheet to after the last worksheet
Sheets("ICS 214").Select
Sheets("ICS 214").Copy Befo=Sheets(2)
'delete constants, retaining formulas in the range "d29-d46"
Range("D29:d46").SpecialCells(xlConstants).ClearCo ntents
'
End Sub
"David McRitchie" wrote:
Hi Erik,
actually that would be close to the default which might be acceptable
if not you can do the rename as previously mentioned, but you would have
to test for error or test beforehand for preexistence..
Edit, Move or Copy Sheet, create a copy
or the same in a recorded macro (I would change Before to After)
Sub Macro2()
Sheets("new_work").Copy Befo=Sheets(1)
End Sub
original sheet: New_Work
copied sheet: New_Work (2)
---
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 ...
Can they be set up so they keep the same name but just add copy2 copy3 etc to
the end of the name?
"David McRitchie" wrote:
Hi Eric,
Didn't realize that code without the quotes was from me,
Renaming sheets within a macro is going to be problematic.
You could intercept the error and try again, or ignore it keeping the
name assigned by Excel and manually change it later.
Possibly you could name your sheets
so that they are guaranteed to be unique with a
date and timestamp format yyyy-mm-dd-mmss
http://www.mvps.org/dmcritchie/excel/backup.htm
http://www.mvps.org/dmcritchie/excel/sheets.htm
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 ...
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