Need help with macro to copy, paste, and clear cell values

[Anthony]

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?

 

[Don Guillett]

As ALWAYS, post your code for comments

 

[Anthony]

As ALWAYS, post your code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software





- 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.

 

[Don Guillett]

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
(e-mail address removed)

As ALWAYS, post your code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message





- 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.

 

[Anthony]

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

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

As ALWAYS, post your code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
message

- 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 Guillett]

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
(e-mail address removed)
It worked great. Is there a way to make this work more than one time
though using relative references?

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
message

As ALWAYS, post your code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
in
message

- 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 -

 

[Anthony]

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.

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

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

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
message

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 -

 

[Don Guillett]

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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.

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
message

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

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
in
message

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 -

 

[Don Guillett]

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
(e-mail address removed)

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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.

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
message

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

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
in
message

On Dec 19, 8:42 am, "Don Guillett" <[email protected]> wrote:

As ALWAYS, post your code for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)"Anthony" <[email protected]>
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 -