Copying Formulas

[howard]

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

 

[viquarshaikh]

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.

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

 

[howard]

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