VBA Refresh data and copy worksheet, paste values only.

[natheplas]

Hi All,

I've built a workbook that calculates OTIF for our orders for work.

The issue I have is the workbook now contains so much data, excel hangs when refreshing and unless I leave it for 30mins, it crashes.

I want to know if it's possible to have a VBA that runs, so it refreshes the data, saves it and also copies the worksheet I am interested in.

Saving the summary sheet to another workbook, but pasting values only.

The reason I need it like this is because, I can't copy and paste the cells without it crashing.

However, if I can set up an automated routine with a batch file, that refreshes, saves and copies values to another workbook that would be ideal.

Please can someone help me with this? Please let me know if you need any more information.

Thank you

 

[JumboCactuar]

can be done with vba but first maybe you can clean up your workbook ?
shouldnt take 30 mins for anything

 

[natheplas]

It's because it's linked to a Sage 50 database across a big network. So it's refreshing slowly, then it has a few array formulas in there which seem to make it even slower.

Not sure how I could make it cleaner really.

We will have SQL soon, so reports like this will run no problem, however, before that happens it's a struggle.

 

[JumboCactuar]

An option would be with VBA:
* Refresh Data
* Write formulas to cells
* Convert formulas to values

Would imagine this would speed things up a fair bit

 

[natheplas]

Thank you