Autopaste as value

[Byron]

I have a report that looks something like this:

DATE​

TIME​

TECHNICIAN​

PART # ​

EMPLOYEE​

9/5/2013 12:00 PM JOHN 123 BRYAN
9/6/2013 1:30 PM ROBERT 456 JOSEPH

​

​

​

​

​

Columns A and B are formulas that fill in after I enter information in column E. I'm looking for a macro/formula that automatically pastes as value everything from the last record enter up so everytime I open the file, that information doesn't change. The entire columns A and B are formulas that will be changing to value when a new record is entered.

Thank you.

 

[Sam Mathai Chacko]

Paste this in the Sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Column = 5 Then
        With Me.UsedRange.Range("A1:B" & Me.UsedRange.Rows.Count)
            .Value2 = .Value2
        End With
    End If
   
End Sub

 

[Byron]

I'm not that familiar with VB so I must be doing something wrong because I went to VB window and added a module. Then copied the macro above and save it. Went back to my report and tried by entering info in column 5 but all formulas above change as well. What I wanted is that everytime I enter a new line of info, the lines above change from formulas to values, including columns A and B already filled with information.

 

[Byron]

Ok, I re-checked the macro above and what it does is that it automatically pastes everything as value, losing the formulas for the rows with no information.

 

[SirJB7]

Hi, Byron!
I don't understand your requirement if the code provided by Sam Mathai Chacko doesn't work. However it should be placed in the class module of the related worksheet and not in any standard or other class module.
If still fighting with the issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!