automatically copy and paste as values to protected sheet 2

[reylan]

Please help me how to automate copy entire row sheet 1 as values only (not the formula) to protected sheet 2(where payment are recorded).
Also upon a change in a specific change of cell A1 in sheet1 the Max # in coloumn AB automatically add 1. These two sheets are protected

Thank You.

 

[Smitty]

Welcome to the Board!

It sounds like a change event would work, but not without more detail.

Your best bet to start is to record a macro doing the following:

1) Goto the destination sheet and unprotect it
2) Return to the source sheet and copy the range you want
3) Go back to the destination sheet and Edit-->Paste Special-->Values
4) Reprotect the destination sheet

Once you've done that post the resulting code and someone can clean it up for you.

As for this part:

Also upon a change in a specific change of cell A1 in sheet1 the Max # in coloumn AB automatically add 1. These two sheets are protected

How about we get through #1 first?

 

[reylan]

In the same workbook sheet 1 and sheet 2 are protected

In sheet 1 the values in every rows resulted by formulas like(lookup function) when A3 cell is greater than 1 the entire row will be automatically copied and paste as values (not the formula) to sheet 2. same results goes for the rows.

Private Sub Worksheet_Change(ByVal Target as Range)
If Target.Column <= 1 And Target.Value = "1" Then Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

I used this but results does not satisfied me because formulas are copied to sheet 2 when A1 is 1 and it won't work on protected sheets.

Example:

Sheet 1

A1 B1 C1 D1
1 4+5=9 9+1=10 10+1=11

Sheet 2 Looks like this
A1 B1 C1 D1
1 9 10 11

When another transaction in sheet 1

A1 B1 C1 D1
1 20+5=25 25+1=26 26+1=27

The Results in sheet 2
A1 B1 C1 D1
1 9 10 11
1 25 26 27

 

[Smitty]

Then you need to 1) unprotect the destination sheet and 2) paste special-->values.

If...Then...Target.EntireRow.Copy 
  With Sheets("Sheet2")
     .Unprotect
        .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues,
     .Protect
  End With

 

[reylan]

How can I make this code work on protected sheets without manually unprotecting the sheets and manually protect it again because I wanted the users not to make any changes except for those unprotected cells.


Private Sub Worksheet_Change(ByVal Target as Range)
If Target.Column <= 1 And Target.Value = "1" Then Target.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub


If...Then...Target.EntireRow.Copy
With Sheets("Sheet2")
.Unprotect
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues,
.Protect
End With</pre>
I don't know how to use the code you sent to m....

 

[Smitty]

Like so:

Private Sub Worksheet_Change(ByVal Target as Range)
   .Unprotect
      If Target.Column <= 1 And Target.Value = "1" Then 
        Target.EntireRow.Copy
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues,
      End If
   .Protect 
End Sub

EDIT: Note that I closed your Original Post, however in the future we ask that you stick to only one thread per relevant topic. It helps keep people from doubling their efforts on your behalf. If you feel like your post has fallen too far back you can always respond to it with "Bump" as the topic, which will move it back to the top of the list. And if your question isn't getting any responses maybe you need to consider clarifying what you want or give more detailed explanation.

 

[reylan]

Thanks