Compare two datasets in Excel VBA

I made the following code to take two reports and compares them to show the end user elements which are missing from one of the reports so they can make the adjustments needed.

This is the main part of the process where the data within the two reports are processed and it's working with around 85K lines in one report and 60K lines in the other which are located on sheet1 and sheet2 within the same workbook (an earlier macro clears and pulls the data in from where they live.

It's taking around 15 minutes to run (I've got a quad core machine, with 4gb of ram. takes over an hour to run on the older dual core machines in the office).
Still easier than running it manually but it was suggested that this could be run in seconds with some improvements.

```
Sub processdata()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False

Dim XXXXLen As Long
With Sheets("Input - XXXXwebnew")
XXXXLen = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'add concatenate ref column in column A on Input XXXXWebNew

Sheets("INPUT - XXXXwebnew").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Sheets("INPUT - XXXXwebnew").Range("A1:A" & XXXXLen) = "=CONCATENATE(E1,""_"",G1,""_"",I1)"
Application.Calculate
Sheets("Input - XXXXwebnew").Range("a1:a" & XXXXLen).Copy
Sheets("Input - XXXXwebnew").Range("a1:a" & XXXXLen).PasteSpecial xlPasteValues

'picks up config products and moves them from E (input - XXXXwebnew) to to A on (workings) tab

Workbooks("workingmodel.xlsm").Sheets("WORKINGS").Range("a2:a" & XXXXLen + 1).value _
= Workbooks("workingmodel.xlsm").Sheets("INPUT - XXXXWebNew").Range("e1:e" & XXXXLen).value

'picks up simple products and moves them from A (input - XXXXwebnew) to to A on (workings) tab

'set a second dim which is the dim XXXXlen X2

Dim XXXXlen2 As Long
XXXXlen2

One possible speedup would be to remove all copy/ pastespecial values and just do a single one at the end, just after turning calculation back on:

Sheets("workings").Range("C2:I" & WorkLen).Value2 = Sheets("workings").Range("C2:I" & WorkLen).Value2

In addition, since you are looking up the same information over and over (all VLOOKUP functions share the same first arguments), you should consider adding a column which holds the MATCH function and from the other columns use its result as an argument for the INDEX function.

So suppose we'll use column Z for the MATCH:

Sheets("workings").Range("Z2:Z" & WorkLen) = "=MATCH(A2," & XXXXalllookup & ",0)"

Then column D would become (it fetches its data from col B):

Sheets("workings").Range("d2:d" & WorkLen) = "=INDEX(" & "'" & sheetXXXX_all & "'!$B$1:$B$" & XXXXallLen & ",Z2)"

StackExchange Code Review Q#158610, answer score: 2