VBA & Macro Problems

How to Update Excel Records from a Separate Price List using VBA

Steven Parker Oct 15, 2026 870 views

Question details

The user needs to update prices in an Excel database sheet based on item numbers found in a separate price list sheet.

How to Update Excel Records from a Separate Price List using VBA

Product: Excel
Device & OS: not provided
Scenario: Managing large datasets where a master database requires bulk price updates from a secondary sheet, potentially involving duplicate update entries.
Observed behavior: The user wants to match item numbers across two sheets and replace the corresponding prices automatically using VBA.

Before you start

Before running any VBA macros, always save a backup copy of your Excel workbook, as actions performed by macros generally cannot be undone with the standard Undo function.

Solution 1Recommended

Use a VBA Macro to Match and Update Prices

Run a customized VBA script to automatically cross-reference item numbers between two sheets and update the prices in bulk.

Using a Visual Basic for Applications (VBA) macro is the most efficient way to cross-reference data between two sheets. This approach eliminates manual copying and pasting, making it ideal for large datasets.

The logic of the macro assumes your database is on Sheet1 and your update sheet is on Sheet2. It loops through the item numbers in column A of Sheet2, finds the match in column A of Sheet1, and overwrites the price in column B.

Access the Visual Basic Editor

In your Excel workbook, press ALT + F11 to open the VBA Editor. Alternatively, navigate to the Developer tab on the ribbon and click 'Visual Basic'.

Insert a New Module

In the VBA Editor, click 'Insert' from the top menu and select 'Module' to create a blank script window where you can write your code.

Input the Update Script

Paste your macro code into the module. Ensure the code logic identifies Sheet2 as the source, searches for the corresponding item number in Sheet1, and assigns the new price value to the correct cell.

Run the Macro

Click anywhere inside your pasted code and press F5, or click the green 'Run' button in the top toolbar to execute the script and update your database.

Use a VBA Macro to Match and Update Prices

Handling Duplicate Updates: If your update sheet might contain multiple price changes for the same item, extend your VBA code to compare the modification dates and apply only the most recent change.

Automate with WPS Office

Easily Manage VBA Macros and Large Datasets with WPS Spreadsheet

WPS Spreadsheet provides robust, built-in support for VBA macros, allowing you to automate data updates, cross-reference sheets, and manage complex databases efficiently without switching software.

1. Open Your Workbook: Launch WPS Spreadsheet and open your macro-enabled workbook or standard dataset.
2. Enable the Developer Tab: Navigate to the Tools tab and click 'Developer' to access the macro environment.
3. Run Your Macros: Click 'Visual Basic Editor', paste your update script, and execute it to automatically match and replace prices.

Full compatibility with Microsoft Excel macro-enabled formats (.xlsm, .xlsx).Advanced VBA editor support for writing and debugging automation scripts.Handles large datasets and complex formulas smoothly without lagging.Free and lightweight alternative to heavy spreadsheet applications.

microsoft office alternative - wps office

Frequently Asked Questions

How do I display the Developer tab in Excel to use VBA?

Go to File > Options > Customize Ribbon. In the right pane under Main Tabs, check the box next to 'Developer', then click OK. The tab will now appear on your ribbon.

Can I undo changes made by a VBA macro?

No, actions executed by a VBA macro typically clear the undo stack and cannot be reversed using Ctrl+Z. Always create a copy of your file before running a new script.

What if my item numbers and prices are in different columns?

You need to adjust the column references in your VBA code. For example, if your item numbers are in column C instead of A, change the range definitions in your macro from 'A' to 'C' before running it.

Excel & Data Problems