Macros/VLookup

Resolved · High Priority · Version 2016

Tue 6 Feb 2018, 14:00 Delegate Fiona said...

Fiona has attended:
Excel Intermediate course
Excel Advanced course

Macros/VLookup

Hello,

I currently have a spreadsheet of data that is becoming too large, it is very slow and takes a long time to save.

All the data is date specific and we really only need access to the data from the last year or so.

Is there a way to automatically move the data from one workbook to another when it becomes over a year old so we can have a current and 'archive' workbook?

Thanks

Tue 6 Feb 2018, 17:53 Trainer Jens said...

RE: Macros/VLookup

Hi Fiona,

Thank you for the forum question.

Yes if you have a lot of VBA knowledge and SQL knowledge you can write a code to do what you want.

If it was me I would use Power Pivot to handle the data. Power Pivot can without any problems handle millions of records.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 13 Feb 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Changing Excel file and worksheet defaults

The appearance of any new Excel files or any new worksheets that are inserted into a file are controlled by two template files, Book.xlt and Sheet.xlt.

By opening, modifying and saving these templates you can change the default settings for all new files and/or all newly inserted sheets.

Use Book.xlt to make change to defaults for new workbooks; and Sheet.xlt to change defaults for sheets.

If you can't find either of these files on your computer, you can create and save them yourself.

You can do this simply by creating a new workbook with the setting you want to use as your defaults; then saving them with the appropriate name in the XLStart folder. If you are using Office 2003, this is usually found in C:\Program Files\Microsoft Office\Office11\XLStart.

View all Excel hints and tips