I am creating an estimation tool, where users can input part numbers and quantities, and the tool will output the lowest cost for that part number based on searches through multiple databases.
I accomplish this by loading data into multiple sheets and then evaluate all possibilities on one sheet.
I believe my code is struggling, because I have multiple instances of this loop below which I'm hoping someone can help me improve. The code below runs 3 separate times, for 3 different sheets. There can be up-to 1000 part numbers run at a time. Once the tool has been run, to start over I delete all the sheets, so each sheet is created every time the macro runs.
'Add Content to Summary
'MPN and Qty add to Summary page
Sheet22.Visible = True
Sheet1.Select
Range("A2").Select
Sheet22.Select
Range("A2").Select
For i = 1 To 3
Sheet1.Select
If Len(ActiveCell.Value) > 0 Then
xmpn = ActiveCell.Value
xqty = ActiveCell.Offset(0, 1).Value
Sheet22.Select
ActiveCell.Value = xmpn
ActiveCell.Offset(0, 1).Value = xqty
ActiveCell.Offset(1, 0).Select
Sheet1.Select
ActiveCell.Offset(1, 0).Select
Else
i = 10
End If
i = i - 1
Next
If there are 1000 part number entries, this code can take about 45 seconds to run, and causes excel to show not responding. Any help or suggestions to improve would be greatly appreciated.