vba to speed up macro

@dulitul,

Would it be possible for you to describe in plain text what you are trying to achieve with the formula's. In other words what are you trying to retrieve from the tracker worksheet?

Hello Ollie,

The description is as follows:

in sheet 'SAP 5 Download' I have got three columns where I paste the data from SAP

col. A , B , C

Then I want to update column C in sheet 'SAP 5 Tracker' with column C from 'SAP 5 Download' if there is any match between columns A-B from 'SAP 5 Download' with columns D-E from 'SAP 5 Tracker' then the data from column C in 'SAP 5 Download' should be transferred in column C in 'SAP 5 Tracker'.

Example : A5-B5 in 'SAP 5 Download' equal D55-E55 in 'SAP 5 Tracker' then the value from C5 'SAP 5 Download' is pasted into C55 in 'SAP 5 Tracker' . And this is what I have to do for 18 columns with 10 000 rows each. That's why I use the index and match foruma. I hope it's now clear.

Would you be OK with a VBA solution that you call after the paste of data from SAP?

Noticed in your test file that the download sheet holds columns C, D and E, and the tracker worksheet A, B and C. From your earlier description I thought it was the other way around. My code assumes columns ABC in the download worksheet and DEF in the tracker worksheet. Please advise what is correct?

Please try the following code

Public Sub updateTrackerWorksheet()

'#
'# declare private variables
'#
   Dim pvt_obj_Tracker As Excel.Worksheet
   Dim pvt_obj_Index As Object
   Dim pvt_lng_RowNumber As Long
   Dim pvt_str_KeyValue As String
   
'#
'# create a dictionary object to act as an index for all rows on the tracker
'# worksheet
'#
   Set pvt_obj_Tracker = ThisWorkbook.Worksheets("SAP Tracker")
   Set pvt_obj_Index = CreateObject("Scripting.Dictionary")
   
   With pvt_obj_Tracker
      For pvt_lng_RowNumber = 2 To .Cells(.Rows.Count, "D").End(xlUp).Row
         pvt_str_KeyValue = .Cells(pvt_lng_RowNumber, "D").Value & "|" & .Cells(pvt_lng_RowNumber, "E").Value
         If Not pvt_obj_Index.Exists(pvt_str_KeyValue) Then
            pvt_obj_Index.Add pvt_str_KeyValue, pvt_lng_RowNumber
         End If
      Next pvt_lng_RowNumber
   End With
   
'#
'# process all rows from the SAP download worksheet and for each key (combined column A and B) determine
'# if a matching row exists on the Tracker worksheet - if found updated column C on the tracker worksheet
'# with the value of column C on the download worksheet
'#
   With ThisWorkbook.Worksheets("SAP Download")
      For pvt_lng_RowNumber = 2 To .Cells(.Rows.Count, "D").End(xlUp).Row
         pvt_str_KeyValue = .Cells(pvt_lng_RowNumber, "A").Value & "|" & .Cells(pvt_lng_RowNumber, "B").Value
         If pvt_obj_Index.Exists(pvt_str_KeyValue) Then
            pvt_obj_Tracker.Cells(pvt_obj_Index(pvt_str_KeyValue), "C").Value = .Cells(pvt_lng_RowNumber, "C").Value
         End If
      Next pvt_lng_RowNumber
   End With
   
'#
'# housekeeping
'#
   Set pvt_obj_Index = Nothing

End Sub

1. correct the names of the worksheets in the code
2. remove all formulas from your workbook
3. run the code after pasting the data in the download worksheet

Originally Posted by dulitul

OllieB I have just tested the macro on the same sheet and it does not work.

I have provided revised code. Your original description of the requirement and the test workbook provided later did not match, hence the code obviously did not work either. The new code has been tested against the example workbook and no problems were encountered

@dulitul,

I have adjusted the code to match the example workbook provided by you.

Public Sub updateTrackerWorksheet()

'#
'# declare private variables
'#
   Dim pvt_obj_Tracker As Excel.Worksheet
   Dim pvt_obj_Index As Object
   Dim pvt_lng_RowNumber As Long
   Dim pvt_str_KeyValue As String
   
'#
'# create a dictionary object to act as an index for all rows on the tracker
'# worksheet
'#
   Set pvt_obj_Tracker = ThisWorkbook.Worksheets("SAP Tracker")
   Set pvt_obj_Index = CreateObject("Scripting.Dictionary")
   
   With pvt_obj_Tracker
      For pvt_lng_RowNumber = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
         pvt_str_KeyValue = .Cells(pvt_lng_RowNumber, "A").Value & "|" & .Cells(pvt_lng_RowNumber, "B").Value
         If Not pvt_obj_Index.Exists(pvt_str_KeyValue) Then
            pvt_obj_Index.Add pvt_str_KeyValue, pvt_lng_RowNumber
         End If
      Next pvt_lng_RowNumber
   End With
   
'#
'# process all rows from the SAP download worksheet and for each key (combined column A and B) determine
'# if a matching row exists on the Tracker worksheet - if found updated column C on the tracker worksheet
'# with the value of column C on the download worksheet
'#
   With ThisWorkbook.Worksheets("SAP Download")
      For pvt_lng_RowNumber = 2 To .Cells(.Rows.Count, "D").End(xlUp).Row
         pvt_str_KeyValue = .Cells(pvt_lng_RowNumber, "D").Value & "|" & .Cells(pvt_lng_RowNumber, "E").Value
         If pvt_obj_Index.Exists(pvt_str_KeyValue) Then
            pvt_obj_Tracker.Cells(pvt_obj_Index(pvt_str_KeyValue), "C").Value = .Cells(pvt_lng_RowNumber, "C").Value
         End If
      Next pvt_lng_RowNumber
   End With
   
'#
'# housekeeping
'#
   Set pvt_obj_Index = Nothing

End Sub

If you want to copy more columns please advise on which columns to copy and to which target location

@dulitul,

You are welcome. May I please ask you to mark the thread as closed as per the forum rules and if you are happy with my contribution to click on the star icon. Thank you.