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.