Copying Formulas

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,561
Office Version
  1. 2021
Platform
  1. Windows
I need to set up VBA code to copy formulas in the 2 rows below where there is text but no values to the right. The original values must then be Paste Special Values

When I come to do Nov and I type in Nov, When I activate the macro, the formulas in the row next to Oct must now be copied in the row next to the text Nov and the formulas next to Oct must then contain values only etc. I.e Each time nnew text is entered into Column A and the Macro is activated the formulas from the rows above must be copied to the rows to the right of the text



for eg

Sept 12 135 114 175 (these values contain Formula's)

Oct (the above data to be copied into this row)


Your assistance will be most appreciated


Howard
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Let's say we try it this way. You'll need to have your cursor in the last cell or we could have another variant which picks the last row automatically.

Code:
Sub CopyFormulasDown()
Dim llngRow as Long

llngRow=activesheet.Range("A65536").end(xlup).row

activesheet.range("B" & (llngRow-1) & ":IV" & (llngRow-1)).Copy
activesheet.range("B" & (llngRow) & ":IV" & (llngRow-1)).PasteSpecial xlPasteFormulas

activesheet.range("B" & (llngRow-1) & ":IV" & (llngRow-1)).Copy
activesheet.range("B" & (llngRow-1) & ":IV" & (llngRow-1)).PasteSpecial xlPasteValues

Application.cutcopymode=false


End Sub
Hope this Helps
 
Upvote 0
Copying data with Formulas & range Valuing

Hi Viquarshaikh

Thanks for assisting me with code to copy data with formulas to the next blank row where there is text. The data must be copied to the right of the text and then the original formulas above i.e row 4 & 5 must be range values. The formulas that has just been copied in the row next to where there is text must remain as formulas until text is created in the next month i.e Nov 2006. The formulas in line with Oct 2006 will then be copied into Nov 2006 and the formulas in line with Oct 2006 will then be range valued the following month when the Macro is activated.

It would be appreciated if you would provide me with your email address so that I can email you my workfile






Howard
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top