Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with macro to copy, paste, and clear cell values
I have a work sheet (protected but no password) that contains some
cells with formulas (locked)and some cells in which I will enter numerical values (unlocked). This is the information about the worksheet: range is from A20:K26, cells that require me to enter data are C20 and E22:G26, all other cells contain formulas or values that remain constant (names, header rows, etc.). When I leave G26, I would like to unprotect the sheet, copy entire range from A20:K26 to A27:K33, clear the values in cells C27 and E29:G33, and re-protect sheet. I recorded this macro in Excel 2007 and it worked...the first time but not when I tried to run it again. I did have relative references turned on as this was highlighted on the Developer Tab. Can someone please help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with macro to copy, paste, and clear cell values
On Dec 19, 8:42*am, "Don Guillett" wrote:
As ALWAYS, post your code for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... I have a work sheet (protected but no password) that contains some cells with formulas (locked)and some cells in which I will enter numerical values (unlocked). *This is the information about the worksheet: range is from A20:K26, cells that require me to enter data are C20 and E22:G26, all other cells contain formulas or values that remain constant (names, header rows, etc.). When I leave G26, I would like to unprotect the sheet, copy entire range from A20:K26 to A27:K33, clear the values in cells C27 and E29:G33, and re-protect sheet. *I recorded this macro in Excel 2007 and it worked...the first time but not when I tried to run it again. *I did have relative references turned on as this was highlighted on the Developer Tab. Can someone please help?- Hide quoted text - - Show quoted text - Sorry about that. Here is the code for the copying, pasting, and clearing of the cell. Sub CopyRange() ' ' CopyRange Macro ' ' Keyboard Shortcut: Ctrl+z ' Range("G27").Select ActiveSheet.Unprotect ActiveCell.Offset(-7, -6).Range("A1:K7").Select Selection.Copy ActiveCell.Offset(7, 0).Range("A1").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=8 ActiveCell.Offset(-7, 0).Range("A1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=R[-7]C1+7" ActiveCell.Offset(7, 2).Range("A1:D1").Select Selection.ClearContents ActiveCell.Offset(2, 2).Range("A1:C5").Select Selection.ClearContents ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False End Sub Haven't figured out how to code when leaving cell in record macro yet. Help is appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with macro to copy, paste, and clear cell values
Pls TOP post when replying to ME.
The xl2007 code is somewhat confusing so I went back to your original request withOUT the code. Try this. Right click sheet tabview codecopy paste this. Now, when you put any entry in cell g26 the macro will fire. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < Range("g26").Address Then Exit Sub With ActiveSheet .Unprotect .Range("A20:K26").Copy Range("a27") .Range("c27,e29:g33").ClearContents .Protect DrawingObjects:=False, _ Contents:=True, Scenarios:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... On Dec 19, 8:42 am, "Don Guillett" wrote: As ALWAYS, post your code for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... I have a work sheet (protected but no password) that contains some cells with formulas (locked)and some cells in which I will enter numerical values (unlocked). This is the information about the worksheet: range is from A20:K26, cells that require me to enter data are C20 and E22:G26, all other cells contain formulas or values that remain constant (names, header rows, etc.). When I leave G26, I would like to unprotect the sheet, copy entire range from A20:K26 to A27:K33, clear the values in cells C27 and E29:G33, and re-protect sheet. I recorded this macro in Excel 2007 and it worked...the first time but not when I tried to run it again. I did have relative references turned on as this was highlighted on the Developer Tab. Can someone please help?- Hide quoted text - - Show quoted text - Sorry about that. Here is the code for the copying, pasting, and clearing of the cell. Sub CopyRange() ' ' CopyRange Macro ' ' Keyboard Shortcut: Ctrl+z ' Range("G27").Select ActiveSheet.Unprotect ActiveCell.Offset(-7, -6).Range("A1:K7").Select Selection.Copy ActiveCell.Offset(7, 0).Range("A1").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=8 ActiveCell.Offset(-7, 0).Range("A1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=R[-7]C1+7" ActiveCell.Offset(7, 2).Range("A1:D1").Select Selection.ClearContents ActiveCell.Offset(2, 2).Range("A1:C5").Select Selection.ClearContents ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False End Sub Haven't figured out how to code when leaving cell in record macro yet. Help is appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with macro to copy, paste, and clear cell values
It worked great. Is there a way to make this work more than one time
though using relative references? On Dec 19, 11:57*am, "Don Guillett" wrote: Pls TOP post when replying to ME. The xl2007 code is somewhat confusing so I went back to your original request withOUT the code. Try this. Right click sheet tabview codecopy paste this. Now, when you put any entry in cell g26 the macro will fire. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < Range("g26").Address Then Exit Sub With ActiveSheet *.Unprotect *.Range("A20:K26").Copy Range("a27") *.Range("c27,e29:g33").ClearContents *.Protect DrawingObjects:=False, _ * Contents:=True, Scenarios:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... On Dec 19, 8:42 am, "Don Guillett" wrote: As ALWAYS, post your code for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... I have a work sheet (protected but no password) that contains some cells with formulas (locked)and some cells in which I will enter numerical values (unlocked). This is the information about the worksheet: range is from A20:K26, cells that require me to enter data are C20 and E22:G26, all other cells contain formulas or values that remain constant (names, header rows, etc.). When I leave G26, I would like to unprotect the sheet, copy entire range from A20:K26 to A27:K33, clear the values in cells C27 and E29:G33, and re-protect sheet. I recorded this macro in Excel 2007 and it worked...the first time but not when I tried to run it again. I did have relative references turned on as this was highlighted on the Developer Tab. Can someone please help?- Hide quoted text - - Show quoted text - Sorry about that. *Here is the code for the copying, pasting, and clearing of the cell. Sub CopyRange() ' ' CopyRange Macro ' ' Keyboard Shortcut: Ctrl+z ' * * Range("G27").Select * * ActiveSheet.Unprotect * * ActiveCell.Offset(-7, -6).Range("A1:K7").Select * * Selection.Copy * * ActiveCell.Offset(7, 0).Range("A1").Select * * ActiveSheet.Paste * * ActiveWindow.SmallScroll Down:=8 * * ActiveCell.Offset(-7, 0).Range("A1").Select * * Application.CutCopyMode = False * * ActiveCell.FormulaR1C1 = "=R[-7]C1+7" * * ActiveCell.Offset(7, 2).Range("A1:D1").Select * * Selection.ClearContents * * ActiveCell.Offset(2, 2).Range("A1:C5").Select * * Selection.ClearContents * * ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ * * * * False End Sub Haven't figured out how to code when leaving cell in record macro yet. *Help is appreciated.- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with macro to copy, paste, and clear cell values
Details or
If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... It worked great. Is there a way to make this work more than one time though using relative references? On Dec 19, 11:57 am, "Don Guillett" wrote: Pls TOP post when replying to ME. The xl2007 code is somewhat confusing so I went back to your original request withOUT the code. Try this. Right click sheet tabview codecopy paste this. Now, when you put any entry in cell g26 the macro will fire. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < Range("g26").Address Then Exit Sub With ActiveSheet .Unprotect .Range("A20:K26").Copy Range("a27") .Range("c27,e29:g33").ClearContents .Protect DrawingObjects:=False, _ Contents:=True, Scenarios:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... On Dec 19, 8:42 am, "Don Guillett" wrote: As ALWAYS, post your code for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "Anthony" wrote in message ... I have a work sheet (protected but no password) that contains some cells with formulas (locked)and some cells in which I will enter numerical values (unlocked). This is the information about the worksheet: range is from A20:K26, cells that require me to enter data are C20 and E22:G26, all other cells contain formulas or values that remain constant (names, header rows, etc.). When I leave G26, I would like to unprotect the sheet, copy entire range from A20:K26 to A27:K33, clear the values in cells C27 and E29:G33, and re-protect sheet. I recorded this macro in Excel 2007 and it worked...the first time but not when I tried to run it again. I did have relative references turned on as this was highlighted on the Developer Tab. Can someone please help?- Hide quoted text - - Show quoted text - Sorry about that. Here is the code for the copying, pasting, and clearing of the cell. Sub CopyRange() ' ' CopyRange Macro ' ' Keyboard Shortcut: Ctrl+z ' Range("G27").Select ActiveSheet.Unprotect ActiveCell.Offset(-7, -6).Range("A1:K7").Select Selection.Copy ActiveCell.Offset(7, 0).Range("A1").Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=8 ActiveCell.Offset(-7, 0).Range("A1").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "=R[-7]C1+7" ActiveCell.Offset(7, 2).Range("A1:D1").Select Selection.ClearContents ActiveCell.Offset(2, 2).Range("A1:C5").Select Selection.ClearContents ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False End Sub Haven't figured out how to code when leaving cell in record macro yet. Help is appreciated.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy paste values macro | Excel Discussion (Misc queries) | |||
copy paste cell with value using macro | Excel Discussion (Misc queries) | |||
Macro for copy and paste values | Excel Discussion (Misc queries) | |||
Copy range in macro using paste special values | Excel Discussion (Misc queries) | |||
Macro to open workbook and copy and paste values in to orig workbo | Excel Worksheet Functions |