Vba copy range of formulas, loop through sheet & paste them as values

[Excellover123]

Hi Message Board.
I originally posted this back in oct 2022 but never got any hits. Could any one help me on this?
Just wondering if someone could help/point me in the right direction.
Currently, i have some VBA code that copies a range of cells (Q5:DC5 (This the named range called Master_MRP)) that houses the below/attached formula & this loops through & first pastes as formula & then copies & pastes as values (as per the below/attached snip) - I'm looking to see if there's a better/faster calculating solution?

Thank you for your help/time.

please do let me know if you need more info from me.

Regards, Gary

 

[James006]

Hi,
You could replace everything with following instruction :

Evaluate("=If( H1=""F"",-H2,-SUMIFS('DAILY -MRP Info from RDP'!E2:E50000, 'DAILY -MRP Info from RDP'!A2:A50000, A1&"":""&Text(Q34+F6,""dd/mm/yyyy""))*(1+F3))"

 

[DanteAmor]

Hi @Excellover123, Thanks for posting on the forum.

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting, thereby increasing you chances of getting help.


You should also put the formula here instead of an image, use the icon to paste formulas:

Improvement:
Please put here your complete macro and some examples from your sheet to understand how the formula works and replace the formula with arrays and make the process faster.

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I hope to hear from you soon.
Respectfully
Dante Amor

 

[Excellover123]

Hi @Excellover123, Thanks for posting on the forum.

Note Code Tag:
In future please use code tags when posting code.
How to Post Your VBA Code it makes your code easier to read and copy and it also maintains VBA formatting, thereby increasing you chances of getting help.


You should also put the formula here instead of an image, use the icon to paste formulas:
View attachment 87310


Improvement:
Sub MRP_Update()

On Error Resume Next
ActiveSheet.ShowAllData

Call ImportMRP

Application.ScreenUpdating = False
Application.StatusBar = False
Application.EnableEvents = False

'get column and row values from the settings page
i = Range("NSun_Col").Value 'note does not update the history week
j = Range("First_MRP").Value
k = Range("Last_MRP").Value
l = Range("Interval").Value
m = Range("First_PrevMRP").Value
o = Range("Last_Column").Value
p = m - j 'interval between MRP & PrevMRP rows

For counter1 = j To k

'Copies down Previous MRP
Range(i & counter1 + p & ":" & o & counter1 + p).Value2 = Range(i & counter1 & ":" & o & counter1).Value2

'copy MRP Master to MRP data line
Range("Master_MRP").Copy 'note does not update the history week
Range(i & counter1).PasteSpecial Paste:=xlPasteFormulas
'copy paste values to save on processing
Range(i & counter1 & ":" & o & counter1).Value2 = Range(i & counter1 & ":" & o & counter1).Value2

counter1 = counter1 + l - 1
Next counter1

'update last refresh, apply screen updating and send message
Range("Refresh_MRP").Select
Selection = Now()
Application.ScreenUpdating = True
Application.StatusBar = True
Application.EnableEvents = True
MsgBox ("MRP Update Complete")

End Sub

Sub ImportMRP()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.StatusBar = False
Application.EnableEvents = False
Worksheets("DAILY - MRP Info from RGP").Range("A1:G50000").ClearContents
Workbooks.Open Filename:="S:\Supply Chain\Materials Control\STOCK BOARDS\Buying Forecast.xlsBuying Forecast.xls", ReadOnly:=True
Workbooks("Stock Board Raw Materials - 13 WEEKS.xlsb").Worksheets("DAILY - MRP Info from RGP").Range("A1:G45000").Value2 = Workbooks("Buying Forecast.xlsBuying Forecast.xls").Worksheets("Sheet1").Range("A1:G45000").Value2
Workbooks("Buying Forecast.xlsBuying Forecast.xls").Close savechanges:=False
Worksheets("STOCK BOARD").Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = True
Application.EnableEvents = True
End Sub

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I hope to hear from you soon.
Respectfully
Dante Amor

Thanks you, Dante. Your points have been noted.
I have included 2 Macros as one is called from within the other.
I'm trying to extract some of my worksheet as a sample but it keeps crashing.
Kind regards
Gary

 

[Excellover123]

Hi,
You could replace everything with following instruction :

Evaluate("=If( H1=""F"",-H2,-SUMIFS('DAILY -MRP Info from RDP'!E2:E50000, 'DAILY -MRP Info from RDP'!A2:A50000, A1&"":""&Text(Q34+F6,""dd/mm/yyyy""))*(1+F3))"

Hi James & thank you for your post.
Do you mean add the formula into the vba rather than copying from the sheet?
The only issue with this is there are 91 different formulas that are being copied down. Each formula changes the date as it goes from column to column (I.E. from Q34 to R34, S34, T Etc up to DC.
Regards, Gary