Macro to copy and paste values from a table to remove function and leave data

[Novice86]

I have 2 separate workbooks used to manage transactions, one is the "out" workbook and the other is the back "in" workbook. Both use tables to add transactions. All the transactions details, name, date, type, etc, are manually entered into the "out" workbook table with a tracking # in column A. The "in" workbook is completed by entering tracking number into column A. Columns B:G and O:S use V lookups to populate transaction details from "out" workbook table. Both tables are beginning to grow to a substantial size; the "in" workbook is now very slow to load, I assume, due to all the V lookup functions.

I'm looking for a auto running macro to copy and paste the values from columns B:G and O:S when data is entered into column H. By removing the functions, I assume it will speed up the workbook processing time when filtering, saving, opening closing, etc.

The intention is using the "in" workbook to monitor our process status. My users are now realizing the value in the workbook and are getting impatient waiting for loading. Somebody please help me improve this monster I've created!

 

[JLGWhiz]

Assuming you want the data in O:S and B:G to be the same row as the chang in column H.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
        Range("B" & Target.Row).Resize(1, 6).Copy Range("O" & Target.Row)
    End If
Application.EnableEvents = True
End Sub

Copy the code to the worksheet code module of the sheet you want the action to occur on. To access the code module, right click the sheet name tab, then click 'View Code' in the pop up menu. After you paste the code into the code pane, close the VB editor and save the workbook as a macro enabled workbook to preserve the code. The code will now run when changes are made to the worksheet, but will only execute a copy action if the change is in column H.

I just noticed that your narrative says copy from B:G And O:S. Does that meant there is another destination that you have not listed?

 

[Novice86]

Yes; your assumption was correct, all B:G and O:S copy paste activity in same row as column H data entered into. The macro you provided is copying and pasting formula from B:G into O:S. I am trying to paste data only terminating the formula into same cell. I need B:G to copy and paste values back into B:G and O:S to copy and paste values back into O:S. All of this would ideally happen when column H has data entered.

Thanks!

 

[JLGWhiz]

I need B:G to copy and paste values back into B:G and O:S to copy and paste values back into O:S. All of this would ideally happen when column H has data entered.

This statement is confusing. Out of all this, is the summary that you want to convert the data in B:G And O:S to values only, basically remove the formulas? If so, then this should work

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("H:H")) Is Nothing Then
        With Range("B" & Target.Row & ":G" & Target.Row, "O" & Target.Row & ":S" & Target.Row)
        .Value = .Value
    End With
    End If
Application.EnableEvents = True
End Sub