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?

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?

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("A11").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.

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("A11").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.

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("A11").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 -

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("A11").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 -

Don,
I am using this sheet to calculate the handicaps for a bowling team.
Our handicap will change each week based on our season to date (STD)
single game average. The numbers that I input in E22:G26 are the
three game scores for each of the five players on the team. The
single cell (C20) that is cleared is filled by the opposing team
name. All the other cells our filled by formulas that calculate STD
games, STD pins, STD average, Total games, Total Pins, and Total
Average. The total colums include the numbers which I input. The
last colums is the handicap which is filled by a VLOOKUP formula.
When I exit cell G26 (or the equivalent cell when using a relative
reference) I would like the macro to fire again. Basically, I would
like to use this on a weekly basis to determine our handicap. I hope
this helps and you can assist. If not, I can send the file as per
your post. Thanks.

On Dec 20, 8:28*am, "Don Guillett" wrote:
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("A11").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 -- Hide quoted text -

- Show quoted text -

To test, I really need to see the file with before/after examples and a
complete explanation.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Anthony" wrote in message
...
Don,
I am using this sheet to calculate the handicaps for a bowling team.
Our handicap will change each week based on our season to date (STD)
single game average. The numbers that I input in E22:G26 are the
three game scores for each of the five players on the team. The
single cell (C20) that is cleared is filled by the opposing team
name. All the other cells our filled by formulas that calculate STD
games, STD pins, STD average, Total games, Total Pins, and Total
Average. The total colums include the numbers which I input. The
last colums is the handicap which is filled by a VLOOKUP formula.
When I exit cell G26 (or the equivalent cell when using a relative
reference) I would like the macro to fire again. Basically, I would
like to use this on a weekly basis to determine our handicap. I hope
this helps and you can assist. If not, I can send the file as per
your post. Thanks.

On Dec 20, 8:28 am, "Don Guillett" wrote:
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("A11").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 -- Hide quoted text -

- Show quoted text -

As an Air Force brat and former USAF officer and former long time resident
of Killeen Texas (Ft Hood), I'm glad to help an Army SFC.
Right click sheet tabview codeinsert this to make new block.
'macro looks at last row in col A to determine the last row in col G
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim fr As Long
fr = Cells(Rows.Count, "a").End(xlUp).Row - 6
If Target.Address Cells(fr + 6, "g").Address Then Exit Sub
If Len(Application.Trim(Target)) 1 Then Exit Sub
Rows(fr).Resize(7).Copy Rows(fr + 7)
Cells(fr + 7, "c").ClearContents
Cells(fr + 7 + 2, "e").Resize(5, 3).ClearContents
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
To test, I really need to see the file with before/after examples and a
complete explanation.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Anthony" wrote in message
...
Don,
I am using this sheet to calculate the handicaps for a bowling team.
Our handicap will change each week based on our season to date (STD)
single game average. The numbers that I input in E22:G26 are the
three game scores for each of the five players on the team. The
single cell (C20) that is cleared is filled by the opposing team
name. All the other cells our filled by formulas that calculate STD
games, STD pins, STD average, Total games, Total Pins, and Total
Average. The total colums include the numbers which I input. The
last colums is the handicap which is filled by a VLOOKUP formula.
When I exit cell G26 (or the equivalent cell when using a relative
reference) I would like the macro to fire again. Basically, I would
like to use this on a weekly basis to determine our handicap. I hope
this helps and you can assist. If not, I can send the file as per
your post. Thanks.

On Dec 20, 8:28 am, "Don Guillett" wrote:
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("A11").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 -- Hide quoted text -

- Show quoted text -