| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help modifying a macro
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
retaining formulas in the range d29-d46
Range(D29:D46).SpecialCells(xlConstants).ClearCont ents |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
Hi Eric,
Yes record a macro, turning off protection using the password, and turn protection on with the password. The password will be seen by anyone looking at the macro. Passwords are more to prevent accidents than theft. There may be an option that allows you to do things in a macro that are not allowed to be done manually, but I don't play with passwords. --- 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 ... 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. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
Thanks, My concern is only inadvertent modification and I am not using a
password. It took me a few tries, but I made it create a name and clear the proper range on the second sheet. Unfortunbately it left the protection off of the original. I added a line to add it back to the original.. All I need to know how to do is after it adds the protection back onto the original, how can I make it then go to the newly copied sheet? I am now using the following macro: 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) ActiveSheet.Unprotect 'delete constants, retaining formulas in the range "d29-d46" Range("D29:d46").SpecialCells(xlConstants).ClearCo ntents ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Sheets("ICS 214").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True ' End Sub "David McRitchie" wrote: Hi Eric, Yes record a macro, turning off protection using the password, and turn protection on with the password. The password will be seen by anyone looking at the macro. Passwords are more to prevent accidents than theft. There may be an option that allows you to do things in a macro that are not allowed to be done manually, but I don't play with passwords. --- 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 ... 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. |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
Disregard my last request. I was able to make it work. Thanks very much for
your help with this. "David McRitchie" wrote: Hi Eric, Yes record a macro, turning off protection using the password, and turn protection on with the password. The password will be seen by anyone looking at the macro. Passwords are more to prevent accidents than theft. There may be an option that allows you to do things in a macro that are not allowed to be done manually, but I don't play with passwords. --- 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 ... 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. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need help modifying a macro
good, some boilerplate code for coding involving sheets (not passwords) is in
http://www.mvps.org/dmcritchie/excel/sheets.htm "EAHRENS" wrote Disregard my last request. I was able to make it work. Thanks very much for your help with this. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Editing a simple macro | Excel Worksheet Functions | |||
| Modifying Macro | Excel Worksheet Functions | |||
| macro with F9 | Excel Discussion (Misc queries) | |||
| Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
| Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
Linear Mode
