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
dguillett1@austin.rr.com
"Anthony" <aksaunders@affiniongroup.com> wrote in message 
news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com...
>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=A0am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> As ALWAYS, post your code for comments
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote in=
 message
>
> news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com...
>
>
>
> >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). =A0This 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. =A0I recorded this macro in Excel 2007 and it worked...the first
> > time but not when I tried to run it again. =A0I 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:=3D8
    ActiveCell.Offset(-7, 0).Range("A1").Select
    Application.CutCopyMode =3D False
    ActiveCell.FormulaR1C1 =3D "=3DR[-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:=3DFalse, Contents:=3DTrue,
Scenarios:=3D _
        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 tab>view code>copy 
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
dguillett1@austin.rr.com
"Anthony" <aksaunders@affiniongroup.com> wrote in message 
news:2beb1caf-7b31-498d-89a2-5ec71bdf3db2@1g2000vbe.googlegroups.com...
On Dec 19, 8:42 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> As ALWAYS, post your code for comments
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote in 
> message
>
> news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com...
>
>
>
> >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. 

It worked great. Is there a way to make this work more than one time
though using relative references?

On Dec 19, 11:57=A0am, "Don Guillett" <dguille...@austin.rr.com> 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 tab>view code>copy
> 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
> =A0.Unprotect
> =A0.Range("A20:K26").Copy Range("a27")
> =A0.Range("c27,e29:g33").ClearContents
> =A0.Protect DrawingObjects:=3DFalse, _
> =A0 Contents:=3DTrue, Scenarios:=3DFalse
> End With
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote in=
 message
>
> news:2beb1caf-7b31-498d-89a2-5ec71bdf3db2@1g2000vbe.googlegroups.com...
> On Dec 19, 8:42 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
>
>
>
>
> > As ALWAYS, post your code for comments
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote =
in
> > message
>
> >news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com...
>
> > >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. =A0Here is the code for the copying, pasting, and
> clearing of the cell.
>
> Sub CopyRange()
> '
> ' CopyRange Macro
> '
> ' Keyboard Shortcut: Ctrl+z
> '
> =A0 =A0 Range("G27").Select
> =A0 =A0 ActiveSheet.Unprotect
> =A0 =A0 ActiveCell.Offset(-7, -6).Range("A1:K7").Select
> =A0 =A0 Selection.Copy
> =A0 =A0 ActiveCell.Offset(7, 0).Range("A1").Select
> =A0 =A0 ActiveSheet.Paste
> =A0 =A0 ActiveWindow.SmallScroll Down:=3D8
> =A0 =A0 ActiveCell.Offset(-7, 0).Range("A1").Select
> =A0 =A0 Application.CutCopyMode =3D False
> =A0 =A0 ActiveCell.FormulaR1C1 =3D "=3DR[-7]C1+7"
> =A0 =A0 ActiveCell.Offset(7, 2).Range("A1:D1").Select
> =A0 =A0 Selection.ClearContents
> =A0 =A0 ActiveCell.Offset(2, 2).Range("A1:C5").Select
> =A0 =A0 Selection.ClearContents
> =A0 =A0 ActiveSheet.Protect DrawingObjects:=3DFalse, Contents:=3DTrue,
> Scenarios:=3D _
> =A0 =A0 =A0 =A0 False
> End Sub
>
> Haven't figured out how to code when leaving cell in record macro
> yet. =A0Help 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
dguillett1@austin.rr.com
"Anthony" <aksaunders@affiniongroup.com> wrote in message 
news:d41624a4-9d73-4442-9164-8ef3c648796d@21g2000vbh.googlegroups.com...
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" <dguille...@austin.rr.com> 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 tab>view code>copy
> 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
> dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote in 
> message
>
> news:2beb1caf-7b31-498d-89a2-5ec71bdf3db2@1g2000vbe.googlegroups.com...
> On Dec 19, 8:42 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
>
>
>
>
> > As ALWAYS, post your code for comments
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote 
> > in
> > message
>
> >news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com...
>
> > >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 -

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=A0am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Details or
> =A0 =A0 =A0 If desired, send your file to my address below. I will only l=
ook if:
> =A0 =A0 =A0 1. You send a copy of this message on an inserted sheet
> =A0 =A0 =A0 2. You give me the newsgroup and the subject line
> =A0 =A0 =A0 3. You send a clear explanation of what you want
> =A0 =A0 =A0 4. You send before/after examples and expected results.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote in=
 message
>
> news:d41624a4-9d73-4442-9164-8ef3c648796d@21g2000vbh.googlegroups.com...
> 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" <dguille...@austin.rr.com> 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 tab>view code>cop=
y
> > 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:=3DFalse, _
> > Contents:=3DTrue, Scenarios:=3DFalse
> > End With
> > End Sub
>
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote =
in
> > message
>
> >news:2beb1caf-7b31-498d-89a2-5ec71bdf3db2@1g2000vbe.googlegroups.com...
> > On Dec 19, 8:42 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
> > > As ALWAYS, post your code for comments
>
> > > --
> > > Don Guillett
> > > Microsoft MVP Excel
> > > SalesAid Software
> > > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrot=
e
> > > in
> > > message
>
> > >news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com.=
...
>
> > > >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 da=
ta
> > > > are C20 and E22:G26, all other cells contain formulas or values tha=
t
> > > > remain constant (names, header rows, etc.). When I leave G26, I wou=
ld
> > > > 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 firs=
t
> > > > 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:=3D8
> > ActiveCell.Offset(-7, 0).Range("A1").Select
> > Application.CutCopyMode =3D False
> > ActiveCell.FormulaR1C1 =3D "=3DR[-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:=3DFalse, Contents:=3DTrue,
> > Scenarios:=3D _
> > 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
dguillett1@austin.rr.com
"Anthony" <aksaunders@affiniongroup.com> wrote in message 
news:5be1296f-65d7-4452-9595-072e6de86047@m16g2000yqc.googlegroups.com...
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" <dguille...@austin.rr.com> 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
> dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote in 
> message
>
> news:d41624a4-9d73-4442-9164-8ef3c648796d@21g2000vbh.googlegroups.com...
> 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" <dguille...@austin.rr.com> 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 tab>view code>copy
> > 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
> > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote 
> > in
> > message
>
> >news:2beb1caf-7b31-498d-89a2-5ec71bdf3db2@1g2000vbe.googlegroups.com...
> > On Dec 19, 8:42 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
> > > As ALWAYS, post your code for comments
>
> > > --
> > > Don Guillett
> > > Microsoft MVP Excel
> > > SalesAid Software
> > > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote
> > > in
> > > message
>
> > >news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com...
>
> > > >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 -- 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 tab>view code>insert 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
dguillett1@austin.rr.com
"Don Guillett" <dguillett1@austin.rr.com> wrote in message 
news:OXErWlagKHA.5792@TK2MSFTNGP05.phx.gbl...
> To test, I really need to see the file with before/after examples and a 
> complete explanation.
>
> -- 
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett1@austin.rr.com
> "Anthony" <aksaunders@affiniongroup.com> wrote in message 
> news:5be1296f-65d7-4452-9595-072e6de86047@m16g2000yqc.googlegroups.com...
> 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" <dguille...@austin.rr.com> 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
>> dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote in 
>> message
>>
>> news:d41624a4-9d73-4442-9164-8ef3c648796d@21g2000vbh.googlegroups.com...
>> 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" <dguille...@austin.rr.com> 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 tab>view 
>> > code>copy
>> > 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
>> > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> wrote 
>> > in
>> > message
>>
>> >news:2beb1caf-7b31-498d-89a2-5ec71bdf3db2@1g2000vbe.googlegroups.com...
>> > On Dec 19, 8:42 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>>
>> > > As ALWAYS, post your code for comments
>>
>> > > --
>> > > Don Guillett
>> > > Microsoft MVP Excel
>> > > SalesAid Software
>> > > dguille...@austin.rr.com"Anthony" <aksaund...@affiniongroup.com> 
>> > > wrote
>> > > in
>> > > message
>>
>> > >news:21f3ba6d-0b72-4f23-acf9-a9879be4ac23@h2g2000vbd.googlegroups.com...
>>
>> > > >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 -- Hide quoted text -
>>
>> - Show quoted text -
>