I wrote a code to match data (MaterialPN vs. MaterialPS and WeekPN vs. WeekPS) and copy appropriate values between two sheets (Packaging Needs - PN and Packaging Staging - PS).
I already turned off ScreenUpdating, Calculations and Events. This made the run time go from 5 minutes to 1 minute, which is still quite slow (my data is ~3000 rows only). I also tried forcing an exit of the If Statement when the WeekPN is not equal to WeekPS with the use of GoTo Flag1, but this did not make my code run any faster.
Any tips on how to make this code more efficient?
Thanks in advance for any help!
Sub PackagingNeeds2PackagingStaging()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
With Sheets("Packaging Needs")
i = .Cells(.Rows.Count, 5).End(xlUp).Row
End With
With Sheets("Packaging Staging")
l = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
j = 25
For k = 9 To i
For x = 5 To l
For Z = 14 To j
MaterialPN = Sheets("Packaging Needs").Cells(k, 5).Value
MaterialPS = Sheets("Packaging Staging").Cells(x, 1).Value
WeekPN = Sheets("Packaging Needs").Cells(4, Z).Value
WeekPS = Sheets("Packaging Staging").Cells(x, 12).Value
If WeekPN <> WeekPS Then GoTo Flag1
If WeekPN = WeekPS Then
If MaterialPN = MaterialPS Then
Sheets("Packaging Staging").Cells(x, 19).Value = Sheets("Packaging Needs").Cells(k, Z).Value
End If
End If
Flag1:
Next
Next
k = k + 5
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub
Aucun commentaire:
Enregistrer un commentaire