Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-02-2023, 04:36 AM
tomohawk tomohawk is offline Issue With VBA Windows 10 Issue With VBA Office 2019
Advanced Beginner
Issue With VBA
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default Issue With VBA

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
File Type: docx Code.docx (12.9 KB, 1 views)
Reply With Quote
  #2  
Old 02-02-2023, 06:08 AM
p45cal's Avatar
p45cal p45cal is offline Issue With VBA Windows 10 Issue With VBA Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

try changing to:
Code:
  .Range("T15").Formula = "=Current!E" & Worksheets("Current").Cells(Rows.Count, "E").End(xlUp).Row
Reply With Quote
  #3  
Old 02-02-2023, 06:14 AM
tomohawk tomohawk is offline Issue With VBA Windows 10 Issue With VBA Office 2019
Advanced Beginner
Issue With VBA
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default

Thanks p45cal

That has worked perfectly

Can you explain the change so that I can learn.

Tommy
Reply With Quote
  #4  
Old 02-02-2023, 06:50 AM
p45cal's Avatar
p45cal p45cal is offline Issue With VBA Windows 10 Issue With VBA Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

.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.
Reply With Quote
  #5  
Old 02-02-2023, 06:52 AM
tomohawk tomohawk is offline Issue With VBA Windows 10 Issue With VBA Office 2019
Advanced Beginner
Issue With VBA
 
Join Date: Sep 2020
Location: Dublin
Posts: 48
tomohawk is on a distinguished road
Default

Thanks for the support p45cal
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macros Issue ziggy3822 Project 0 05-04-2018 08:32 AM
PDF Conversion Issue - Background Tiled Image Size Issue dustpie Word 1 10-23-2016 07:33 PM
Is this a PowerPoint issue or a Mac issue? Brian_V PowerPoint 0 05-14-2015 09:19 AM
csv issue QCHA Mail Merge 9 10-30-2014 04:40 AM
New Issue jantoine Outlook 1 09-28-2010 06:08 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:05 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft