Copy / Pasting an updatable formula !!!
Basically i have a formula in made up of certain cells dependent on what the user inputs. Mainly Date and Months.
From these sells and then combine them ie
EG
=A$1$&""&A$2$&""&A$3$ which i then copy and paste special (formula) into another cell.
result being =VLOOKUP(B2,'I:\Finance & Risk\Procurement\Procurement\Overdue Reports\2008\May\[020508.xls]AP To Action'!$B:$H,6,FALSE)
now being a bit of a noob and not being able to script from scrach im using the record macro i basicaly copy the above.
click a hyperlink to open a workbook and pastespecial (formula) into the new work book.
But when its pasted its like it doesnt auto pick up it being a formula and the only way to get it to work is to highlight the "b2" at the beginning of the formula and then actually click the b2 cell (ie to turn it blue) then it works.
Only problem is that as recording the macro - as soon as i hightlight the b2 bit it saves the entre formula in the macro.
this is what the macro looks like doing the copy and paste action and hilighting the b2
Code:
Range("K6").Select
Selection.Copy
Range("C32").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Sheets("Richard").Select
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-5],'I:\Finance & Risk\Procurement\Procurement\Overdue Reports\2008\May\[020508.xls]Richard'!C2:C8,6,FALSE)"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G36"), Type:=xlFillDefault
Range("G2:G36").Select
therefore always picking up from that orignal formula and not the updated one when run (ie the 020508.xls) which would need to change daily.
Basically all i want to do is somehow paste a formula from one workbook to the other and for it to be pick up.
It may be a case i'm not doing something correct earlier or ie creating the formula from multiple cells.
Sorry if it seems a bit long winded, i'm still learning
Dev Shed Forums Sponsor:
May 6th, 2008, 04:09 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
