Lock Cell with IF function

[MariPip]

Hello
I am in desperate need for help with this, so any answer would be really useful!
I have two worksheets, lets call them Worksheet1 and Worksheet2.
Worksheet 1 is a sample formulary with a reset button, so that everytime you fill it and then press the button, a pdf of it is created and the cell on to which we insert the details of each sample are deleted.
Worksheet 2 is a Summary formulary, which collects information from each sample (sample number and some results), which it gets from Worksheet 1 using a simple =IF(...) function.
My problem is that when I reset Workhseet 1 I lose the data that had gone onto Worksheet 2.
So I wish to find a way to lock the cells in Worksheet 1 after they have collected the information from Worksheet 1, or something of that sort.
THANK YOU!

 

[onlyadrafter]

Hello,

how are you resetting Worksheet 1, with VBA code? If so, copying the data in Worksheet 2 as values only can be incorporated into the code, onto another sheet. What do you want to happen with this data after resetting?

 

[MariPip]

Hi, first of all thank you for the answer
well, I've been thinking and an easier way would be to come up with a MACRO (which will be asigned to a button) that copies the value in cell ("B4") (Worksheet1) to the next available cell in C column Worksheet2 (Starting from row 27) and the value in cell ("H20") in Worksheet 1 to the cell from the same row as the other one but in column L.
I hope you got my explanation right

 

[onlyadrafter]

Hello,

does this code work as expected?

Sub copy_sht_1_to_2()
    With Sheets("Sheet1")
        .Range("b4").Copy
        Sheets("Sheet2").Range("C26").End(xlDown).Offset(1, 0).PasteSpecial (xlValues)
        .Range("H20").Copy
        Sheets("Sheet2").Range("L" & Sheets("Sheet2").Range("C27").End(xlDown).Row).PasteSpecial (xlValues)
    End With
End Sub

 

[MariPip]

No, it sends an error message for "Application defined or object defined error" and highlights " Sheets("RESUMO").Range("C27").End(xlDown).Offset(1, 0).PasteSpecial (xlValues)"
Do you know what could be going on??
Thank you for the reply

 

[onlyadrafter]

Hello,

might be that it is going to the last row on the entire spreadsheet, try

Sheets("Sheet2").Range("C65536").End(xlup).Offset(1, 0).PasteSpecial (xlValues)

instead, and

Sheets("Sheet2").Range("L" & Sheets("Sheet2").Range("C65536").End(xlup).Row).PasteSpecial (xlValues)