Using VBA to copy large number of formulas efficiently

jdaywalt

Board Regular
Joined
Jul 18, 2012
Messages
63
Hoping for some VBA assistance. I have a worksheet that contains a master set of formulas in cells AE2:AZ2. Users copy a data file into cells AA5:AD-- (ending row varies). Once the data is copied, the user clicks a button to 'refresh formulas', which then copies the master formulas from row 2 into the data rows AE5:AZ--. It also then replaces the live formulas with values. This macro works fine for most data files imported, however there are a few data files that contain too much data & when the user attempts to run the 'refresh formula' macro, Excel comes back with a "not enough resources" error & the macro bombs. Below is the code I am currently using for this process. Is there a more efficient way to construct my code to avoid the resource error? I've heard there is a VBA procedure that copies data out into array, then pulls it back into Excel (I think??), but I am unfamiliar with this process. Any help would be MUCH appreciated!!
Sub Copy_Formulas()​
Dim Numrecords​
Numrecords = Range("AE1").Value 'cell AE1 is a formula that recognizes #of data records copied into the file. This value is used for multiple purposes.

Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))​
Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value​
Application.CutCopyMode = False​
End Sub​
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try turning calculation off, do the copy, turn calc back on.
Code:
Sub Copy_Formulas()
Dim Numrecords
Numrecords = Range("AE1").Value 'cell AE1 is a formula that recognizes #of data records copied into the file. This value is used for multiple purposes.
Application.Calculation = xlCalculationManual
Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))
Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value
Application.Calculation = xlCalculaionAutomatic
Application.CutCopyMode = False
End Sub
 

jdaywalt

Board Regular
Joined
Jul 18, 2012
Messages
63
Thanks for the response. Unfortunately, that won't work. The whole macro involves copying the formula---then directly after it calculates---converts the results to values. I am not doing a series of independent formula copies, which may be aided by your suggested approach. The full copy occurs at one time and it's really the only thing the macro is doing. I'm looking for a process I'm only vaguely familiar with that uses a variant object(?) to temporarily copy the range out to memory 'in bulk', apply formulas, then paste the resulting values back 'in bulk' to the spreadsheet. Are you familiar with this process? I've heard it makes a big difference in response times when executing.
 

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
Try this .... i think the calculations are not finished yet:

Code:
[INDENT]Sub Copy_Formulas()[/INDENT]
[INDENT=2]Dim Numrecords[/INDENT]
[INDENT=2]Numrecords = Range("AE1").Value  [COLOR=#ff0000]'cell AE1 is a formula that recognizes #of data records copied into the file.  This value is used for multiple purposes.[/COLOR][/INDENT]
[INDENT=2]        Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))

Do While Not Application.CalculationState = xlDone
    Application.Wait (Now + TimeValue("0:00:05"))
    DoEvents
Loop
Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value
[/INDENT]
[INDENT=2]        Application.CutCopyMode = False[/INDENT]
[INDENT]End Sub
[/INDENT]
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks for the response. Unfortunately, that won't work. The whole macro involves copying the formula---then directly after it calculates---converts the results to values. I am not doing a series of independent formula copies, which may be aided by your suggested approach. The full copy occurs at one time and it's really the only thing the macro is doing. I'm looking for a process I'm only vaguely familiar with that uses a variant object(?) to temporarily copy the range out to memory 'in bulk', apply formulas, then paste the resulting values back 'in bulk' to the spreadsheet. Are you familiar with this process? I've heard it makes a big difference in response times when executing.

Yes, you are correct that it would not work the way I had it. But if we move the turn on to automatic to a line before the change to value, it might work. I am sure that it is the calculations that are causing the problem by trying to combine the paste and calculate actions. If you can get the paste completed and then calculate it should work OK.
Code:
Sub Copy_Formulas()
Dim Numrecords
Numrecords = Range("AE1").Value 'cell AE1 is a formula that recognizes #of data records copied into the file. This value is used for multiple purposes.
Application.Calculation = xlCalculationManual
Range("AE2:AZ2").Copy Range("AE5:AZ" & CStr(Numrecords + 4))
Application.Calculation = xlCalculaionAutomatic
Range("AE5:AZ" & CStr(Numrecords + 4)).Value = Range("AE5:AZ" & CStr(Numrecords + 4)).Value
Application.CutCopyMode = False
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Sorry, tried it but did not make a difference. Thanks anyway!

Then the only other solution I can think of is to break your larger input files into smaller packages. That would mean counting the records beforehand and if they exceed a certain count, create two files from one for input to the processing file.
Regards, JLG
 

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
If you want to use the array approach you will need to post the calculations you need performed on each cell.

You can calculate the values in memory and populate the cells at the end.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,651
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top