Jan 21 2022 07:13 AM
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
Jan 21 2022 07:20 AM
Do you want to keep a historic record of prices, or should increased prices simply replace the old ones?
Jan 21 2022 08:27 AM
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