One of the contributors here wrote a VBA code for me some time ago (see attached).

The spreadsheet is an annual record of expenditure, so in a new year, I clear all the data and save the old file (****_2022) as the new file (****_2023).

It selects s starting value in a column (fixed) and also the final value in that column (this is variable)

For some reason, the second part (get the last record in a column) is no longer working.

In fact, the relevant field on the sheet now states "=Current!E1004", which was the last record in that column on last year's spreadsheet.

This has only happened since I saved as a new file.

The macro is saved in personal.xlsb

I was not sure if I could post code directly here, so the code is attached.

Tommy

Attached Files

Code.docx (12.9 KB, 1 views)

try changing to:

Thanks p45cal

That has worked perfectly

Can you explain the change so that I can learn.

Tommy

.UsedRange sometimes doesn't update properly.
.Cells(Rows.Count, "E").End(xlUp).Row
does the equivalent you selecting the bottomost cell in column E (probably E1048576) then on the keyboard tapping End then the up arrow.

Thanks for the support p45cal