Macro throwing out 'memory' error

Hi, I am having 'memory' error issue whilst running a file and wonder if anyone can help tweak the memory to improve the file performance. I am using Excel 2010 version. Thanks in advance.

I have a master file with numerous country tabs as well as reporting tabs which pull data from the country tabs. Initially I had a macro which activated an essbase retrieve function and populated each of the country tabs. As the number of rows increased (close to 4800 rows and columns extednign to 'DR' now), running this macro has become next to impossible. So I have now gone for having a separate file which just refreshes the essbase retrieves (and this works fine). I have set up a macro in the master file which then opens this source file and copypastes values from each of the tabs into the corresponding country tab in the master file. But this throws up a 'Excel cannot complete this task with available resources. Choose less data or close other applications’…this despite me having closed all other applications. File size of master file is 13MB and that of the source file (where the retrieve is done) is 18MB. So, effectively when I do the transfer, I have both files open.

I am attaching the code below. I plan to tweak the code to include a 'Do While' to pull all country data. I started initially but trying to pull in two sheets data only and this is crashing...so obvoulsy trying to run this macro for 12 countries is not going to work!!

Please Login or Register  to view this content.

apologies..I meant ' tweak the macro'!!

Firstly, read this: https://www.mrexcel.com/forum/excel-...clipboard.html

Then google other ways of copying excel data without putting many megabytes on the clipboard.

Then google how much upgrading to 64bit CPU + O/S + Office is and consider converting to that, cos 64bit removes the memory restriction. Well, I say removes.....Excel 32bit has a a 2Gb memory maximum....no matter how much RAM you've got, Excel will only use 2Gb of it. 64bit Excel has an 8Tb limit......ie 8000Gb.

I'm new back here but I think I remember advice that included these bits of code that might be worth looking into:

Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = True 'To Turn on at the end of the code.

and

Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation

That might help until an expert comes along

Originally Posted by xlnitwit

That depends on which version you have, and whether your OS is 32 or 64bit- https://support.microsoft.com/en-gb/...ange-for-excel

That's new, didn't know that. Ta.

Hi, thanks for the help. I have tried plugging in the suggestions given by xlnitwit and Daminc2 but now it seems to be throwing up a 'Run time error '1004': Method 'Open' of object 'Workbooks' failed' and when I click ok, it says 'Can't execte code in break mode'. On clicking debug, it highlights the 'Set wb1 = Workbooks.Open (Filename:=FileToOpen)' code. Could anyone help? Is it still caused by the memory issue?

Please Login or Register  to view this content.

BellyGas..thanks for highlighting the systems/OS issues...since this is a company laptop and network, not sure if I can get much help there...and soon:-(

Just a quick thought: Shouldn't the FileFilter include .xlsm? since the workbook is using Macro's?

As you're only copying the values and number formats I would avoid using copy/paste all together.

See if the below works (untested)

Please Login or Register  to view this content.

Ah. Good to know, wasn't aware of that.

Thanks for pointing out

Thanks all...apologies for the delayed response. Had to sift thru all the data and remove as much stuff as required...not ideal as the lost information should now be collected by other means but at least have got the file to work now.