[VBA] How to Append Values to the Bottom of a Column: Transferring Calculation Results to Another File

2025年12月26日

In this article, I explain how to use VBA to calculate values in one Excel file and append the result to the bottom of a specific column in a separate file.

Background

The goal was to calculate the average value of data in Column E of an opened Excel file and append that average to the bottom of Column C in a different Excel file (the one containing this macro).

The Code

I achieved this using the following code:

Workbooks(strFileName).Activate
Range("E25").Formula = "=AVERAGE(E27:E1000)" ' Note: Wanted to use .End(xlDown) but not implemented yet
Range("E25").Copy

ThisWorkbook.Activate
n = Cells(Rows.Count, "C").End(xlUp).Row + 1
Range("C" & n).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues

Code Explanation

Line 1: Activates the source Excel file where the calculation needs to happen. strFileName contains the name of the file that was previously opened.

Line 2: Sets an AVERAGE formula in cell E25, targeting the range E27 to E1000. (Note: Ideally, I wanted to dynamically specify the range from E27 to the last row of data, but I used a fixed range for this implementation.)

Line 3: Copies the cell (E25) containing the calculated average.

Line 4: Switches focus back to the destination file (the workbook where this macro is written).

Line 5: Retrieves the last row number with data in Column C and adds 1 to it. The result is assigned to the variable n. This is a standard technique for finding the next available blank cell at the bottom of a column.

Lines 6-7: Selects the n-th row in Column C and pastes the copied data using PasteSpecial. By specifying Paste:=xlPasteValues, we ensure that only the calculated value is pasted, not the formula itself.

Future Improvements

In this example, I used a fixed range (E27:E1000) for the average calculation. In the future, I plan to improve this code to make the range dynamic using .End(xlDown) so it automatically adjusts to the amount of data.