automatically copy and paste as values to protected sheet 2

reylan

Board Regular
Joined
Jul 21, 2010
Messages
74
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.
 
Last edited:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jul 21, 2010
Messages
74
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

Legend
Joined
May 15, 2003
Messages
29,536
Then you need to 1) unprotect the destination sheet and 2) paste special-->values.

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

reylan

Board Regular
Joined
Jul 21, 2010
Messages
74

ADVERTISEMENT

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

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Like so:

Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,133,758
Messages
5,660,775
Members
418,594
Latest member
hearsay9999

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top