Need help with formula

[greegan]

Hello to all and thank you for your continued assistance

On a daily basis we paste spec - value/numbers data onto one particular worksheet in column B
Column A has the following

A5
=IF(B5="","-",R5-SUM(S5:AA5))
down to A120
A125
=SUM(A5:A120)
A126
=IFERROR(A125/A126,"-")
A127
=IFERROR(A125/A126,"-")
A128
7:30:00 AM
A129
=IFERROR(A127/A128,"-")

On another sheet it will have a formula to pick up the value in A129 based on the date showing in C1 on the first sheet.
We then copy paste values and the next day we repeat.

I may be lazy but selecting columns A:AC to paste in B:AD seems like its a waste of time if we can use one formula on the second worksheet for all of the formulas above. I'm guessing it would involve an array formula ie {} but I'm not familiar enough with array formulas to do this correctly. So I come to seek help from the experts.

Thank you

-- g

 

[ZAX]

Hi greegan,

Open Visual Basic , insert a new module and enter the following code which will copy columns A:AC and paste
the values in columns B:AD in the same worksheet , try it .

Sub copy_values()
Columns("A:AC").Select
Selection.Copy
Columns("B:AD").Select
ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False
End Sub

mr ZAX

 

[greegan]

Wow, this is an old post and my work finally put me back on this project. Thank you for this post and answer. It worked as expected.