Searching multiple Excel sheets for the cheapest item
Based on the array video provided by TinMan, I now declared the data as an array, and then populate each tab referencing the array.  The code is now instantaneous for 1000s records. 

Thanks TinMan!!

'Set MpnQty array

Dim MpnQty() As Variant
Dim Dimension1 As Long, Dimension2 As Long

Sheet1.Activate

Dimension1 = Range("A2", Range("A2").End(xlDown)).Cells.Count - 1
Dimension2 = 1

ReDim MpnQty(0 To Dimension1, 0 To Dimension2)

For Dimension1 = LBound(MpnQty, 1) To UBound(MpnQty, 1)
    For Dimension2 = LBound(MpnQty, 2) To UBound(MpnQty, 2)
        MpnQty(Dimension1, Dimension2) = Range("A2").Offset(Dimension1,   Dimension2).Value
    Next Dimension2
Next Dimension1

'Add MPN and Qty to Summary page

Sheet22.Visible = True
Sheet22.Activate

For Dimension1 = LBound(MpnQty, 1) To UBound(MpnQty, 1)
    For Dimension2 = LBound(MpnQty, 2) To UBound(MpnQty, 2)
        Range("A2").Offset(Dimension1, Dimension2).Value = MpnQty(Dimension1, Dimension2)
    Next Dimension2
Next Dimension