PDA

View Full Version : Solved: Fill Formula down & paste values up



anandbohra
11-13-2007, 02:53 AM
Hi Guys
pl can anybody help me to frame this "For Next loop" for me
What i want to do?
First go to each sheet - go to last record at column B
say last one is B30 so i want to fill down formula of B30 to U30 & fill it down 1 row so the same formula will be at B31 to U31 ( Selection.FillDown)

now whatever formula is still there in range B30 to U30 paste the same as Values. this process has to be done in each sheet

Hope u r clear with my query
awaiting for an earliest reply.

Bob Phillips
11-13-2007, 03:12 AM
Off The top



For Each sh In Activeworkbook.Worksheets
LastRow = sh.Cells(sh.Rows.Count,"B").End(xlUp).Row
sh.Cells(LastRow,"B").Resize(,20).AutoFill sh.Cells(LastRow,"B").Resize(2,20)
sh.Cells(LastRow + 1,"B").Resize(,20).Value = sh.Cells(LastRow + 1,"B").Resize(,20).Value
Next sh

anandbohra
11-13-2007, 03:42 AM
Thank you very much
exactly what i needed.

hibernater
11-13-2007, 05:22 AM
excellent!