Using Macros to monitor stock

Hi there,
We have two spreadsheets. One we use to import into our website and one from our supplier which has stock details on.

What I would like to do is have a macro which copies the product reference from our spreadsheet (catalognew.csv) and find it in our suppliers spreadsheet (products.csv) and copy the amount they have in stock into our worksheet.

I can work out how to do this for one product but I would like to figure out how one macro could do it for a whole load of them. Attached is the sample spreadsheets.

Hello eshopbrokers,

Welcome to the Forum!

I have a cuople of questions about the worksheets. Which column on the "Products" worksheet holds what they have in stock? It also appears your product reference appears in columns "A" and "F". Which column will be used for comparison?

Thanks,
Leith Ross

Hi Leith,
Thank you for responding to my query. Column A in products is the product reference and column D in products is the amount in stock.

Like I said before I can create a macro to gather the stock information for one product, but I dont have a clue of how to do it for loads of different entries.

Best Regards
Toby

eshopbrokers

Hello eshopbrokers,

This is a simple way to check and copy. However, if you have large amounts of data, 10000+ rows, you might what to use a more complex but faster macro. I have the stock level copying onto column "L" (marked in blue in the code). You change this to what you to use. Place this macro in the Catalog Workbook.

Macro Code

Sub CopyStockLevels()

  Dim CatalogWks As Worksheet
  Dim I As Long
  Dim J As Long
  Dim ProductRef
  Dim ProductWks As Worksheet
  
    Set CatalogWks = Workbooks("CatalogNew.csv").Worksheets("CatalogNew")
    Set ProductWks = Workbooks("Products.csv").Worksheets("Products")
    
      For I = 2 To CatalogWks.UsedRange.Rows.Count + 1
        ProductRef = CatalogWks.Cells(I, "K")
          For J = 1 To ProductWks.UsedRange.Rows.Count
            If ProductWks.Cells(J, "A") = ProductRef Then
               CatalogWks.Cells(I, "L") = ProductWks.Cells(J, "D")
            End If
          Next J
      Next I
      
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross

Thank you very much, it works perfectly!

Toby