VBA & Macro Problems

How to Copy the Last and Second-to-Last Values Using VBA in Excel

Natalie Taylor Oct 21, 2026 868 views

Question details

The user wants to write an Excel VBA macro that can automatically locate the last and second-to-last used values in a specific column on one worksheet and copy them to designated cells on another worksheet.

How to Copy the Last and Second-to-Last Values Using VBA in Excel

Product: Excel
Device & OS: not provided
Scenario: Automating the extraction of the most recent data entries (the bottom rows) from a continually updating dataset column.
Observed behavior: The correct VBA syntax is needed to identify the dynamic last row containing data using the End(xlUp) method, subtract one for the second-to-last row, and assign those values to a destination range.

Before you start

Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools in your ribbon to access the Visual Basic Editor.

Solution 1Recommended

Use VBA to Find and Copy the Last Value

Identify the last used row in a specific column using the End(xlUp) method and copy its value to the destination sheet.

The most reliable way to find the last row containing data in a column is to start from the very bottom of the worksheet and look upwards. This prevents blank cells in the middle of your dataset from breaking the macro.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor, then click 'Insert' > 'Module' to create a new script window.

Enter the Last Value Macro

Copy and paste the following code: Sub CopyLastValue() Dim lastRow As Long lastRow = Worksheets("INBOUND").Cells(Rows.Count, "J").End(xlUp).Row Worksheets("TOTALS").Range("F9").Value = Worksheets("INBOUND").Cells(lastRow, "J").Value End Sub

Run the Macro

Press F5 or click the Run button to execute the code. The last value in column J of the INBOUND sheet will be copied to cell F9 on the TOTALS sheet.

Dynamic Range Adjustment: This method automatically calculates the last row every time it is run, making it perfect for datasets that constantly receive new entries at the bottom.

Solution 2

Modify the Macro for the Second-to-Last Value

Adjust the row index by subtracting 1 from the last row variable to grab the preceding value.

Advanced Macros in WPS Spreadsheet

Automate Data Tasks with WPS Office Spreadsheet Macros

WPS Office Spreadsheet provides excellent support for VBA macros. You can run the exact same VBA scripts to find the last and second-to-last values dynamically without needing to alter your code.

1. Open Your Workbook: Launch WPS Spreadsheet and open your Macro-Enabled Workbook (.xlsm).
2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic Editor'.
3. Insert the Code: Click 'Insert' > 'Module' and paste the CopyLastValue or CopySecondLastValue VBA code.
4. Execute the Macro: Run the macro to instantly extract and copy the bottom values from your INBOUND sheet to the TOTALS sheet.

Seamless compatibility with Microsoft Excel VBA code and .xlsm files.Easily identify last rows and copy data across sheets dynamically.Lightweight, fast, and free to use with a familiar ribbon interface.

Frequently Asked Questions

Why is my VBA macro selecting a blank cell as the last value?

If your column contains formatted empty cells or invisible spaces, the `End(xlUp)` method might count them as populated. Ensure you clear all formats and contents from the empty cells below your actual dataset.

Can I use this VBA method for columns other than J?

Yes, simply change the column letter in the `.Cells(Rows.Count, "J")` section of the code to your desired column letter, such as "A" or "C".

How do I trigger this macro automatically when new data is added?

You can trigger the macro automatically by placing your code inside a `Worksheet_Change` event on the INBOUND sheet. This ensures the TOTALS sheet updates the moment a new value is typed into the target column.

Will this VBA code to find the last row work in WPS Office?

Yes. WPS Spreadsheet has robust support for Excel VBA macros. You can run the `xlUp` method and manipulate worksheet ranges natively within WPS just as you would in Microsoft Excel.

Excel & Data Problems