Price Increase from my supplier

Copper Contributor

Hi,

I have a spreadsheet with my product part numbers in one column and prices in the other, I have been sent a spreadsheet with price increases in the same format (part numbers in one column, prices in the other) by my supplier, Is there a way to match part numbers from the new spreadsheet to my spreadsheet part numbers with the update prices to transfer across?

 

On my data sheet is lots of other info as I import this into my website

 

regards

Liam

3 Replies

@maxtreme01 

Do you want to keep a historic record of prices, or should increased prices simply replace the old ones?

simply replace them

@maxtreme01 

Here is a macro you can run:

Sub ReplacePrices()
    ' Assumption: part numbers in column A, starting in row 2.
    ' Prices in column P.
    Dim wss As Worksheet
    Dim wst As Worksheet
    Dim m As Long
    Dim rgs As Range
    Dim rgt As Range
    Application.ScreenUpdating = False
    ' Change the following two lines as needed
    Set wss = Workbooks("New.xlsx").Worksheets("New Prices")
    Set wst = Workbooks("Old.xlsx").Worksheets("Current Prices")
    m = wss.Range("A" & wss.Rows.Count).End(xlUp).Row
    For Each rgs In wss.Range("A2:A" & m)
        Set rgt = wst.Range("A:A").Find(What:=rgs.Value, LookAt:=xlWhole, MatchCase:=False)
        If Not rgt Is Nothing Then
            wst.Range("P" & rgt.Row).Value = wss.Range("P" & rgs.Row).Value
        End If
    Next rgs
    Application.ScreenUpdating = True
End Sub