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:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,298
Messages
5,510,474
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top