excel - VBA paste formulas from filtered column -

is there way in excel(vba) copy/paste formulas filtered column in 1 statement ? works :

sheets(1).range("a2:c" & lastrow).copy sheets(2).range("a2:c" & range("d" & rows.count).end(xlup).row).pastespecial xlpasteformulas 

but returns messed rows (probably because column filtered) :

sheets(2).range("a2:c" & range("d" & rows.count).end(xlup).row).formula = sheets(1).range("a2:c" & lastrow).formula 

any ideas if it's possible without using clipboard, in 1 statement ?

edit

in sheet1, add formulas columns a,b , c:

with sheets(1)     lastrow = .range("d" & rows.count).end(xlup).row     .range("a5:a" & lastrow).value = "=d5/$a$3*100"     .range("a:ag").autofilter field:=22, criteria1:=">=1/1/2014", operator:=xland, criteria2:="<=12/31/2014"     .range("b5:b" & lastrow).specialcells(xlcelltypevisible).value = "=d" & .usedrange.offset(5, 0).specialcells(xlcelltypevisible).row & "/$b$3*100"     .range("a:ag").autofilter field:=22, criteria1:=">=1/1/2015"     .range("c5:c" & lastrow).specialcells(xlcelltypevisible).value = "=d" & .usedrange.offset(5, 0).specialcells(xlcelltypevisible).row & "/$c$3*100"     .showalldata end 

therefore column has formula "=dn/$a$3*100, n row number. b , c formulas have division b3 , c3 cell value. filter sheet1, copy filtered rows , paste them sheet2

sheets(1).range("a4:ag" & lastrow).autofilter field:=7, criteria1:=name sheets(1).range("a5:c" & lastrow).copy sheets(2).range("a5:c" & range("d" & rows.count).end(xlup).row).pastespecial xlpasteformulas 

this can done bringing formula worksheet presents problems. formula can picked in loop needs have cell addresses modified reflect original worksheet name. if application.convertformula method applied , formula converted strictly xlabsolute style, each $ examined see if prefacing original worksheet name appropriate. formula you've supplied (e.g. =dn/$a$3*100) straightforward , shouldn't present problems parsing out.

sub copy_filtered_formulas()     dim lr long, lc long, rvis range     dim vr long, vc long, sfrml string, p long     dim ws1 worksheet, ws2 worksheet      set ws1 = sheets("sheet1")     set ws2 = sheets("sheet2")      ws2         if not isempty(.cells(5, 1))             .range(.cells(5, 1), .cells(rows.count, 1).end(xlup))                 .resize(.rows.count, 3).clearcontents             end         end if     end      ws1         if .autofiltermode .autofiltermode = false         lc = .range("ag:ag").column         lr = .cells(rows.count, 1).end(xlup).row         .cells(4, 1).resize(lr - 3, lc)             .offset(1, 0).resize(.rows.count - 1, 3)                 .formula = "=$d5/a$3*100"             end             .autofilter field:=7, criteria1:=0             .offset(1, 0).resize(.rows.count - 1, 3)                 if cbool(application.subtotal(103, .cells))                     each rvis in intersect(.specialcells(xlcelltypevisible), .specialcells(xlcelltypeformulas))                         sfrml = application.convertformula(rvis.formular1c1, xlr1c1, xla1, xlabsolute, rvis)                         p = instr(1, sfrml, chr(36))                         while cbool(p)                             if asc(mid(sfrml, p + 1, 1)) >= 65 , _                               asc(mid(sfrml, p + 1, 1)) <= 90 , _                               asc(mid(sfrml, p - 1, 1)) <> 33 , _                               asc(mid(sfrml, p - 1, 1)) <> 58                                 sfrml = left(sfrml, p - 1) & chr(39) & .parent.name & chr(39) & chr(33) & mid(sfrml, p, 999)                                 p = instr(p + len(.parent.name) + 5, sfrml, chr(36))                             else                                 p = instr(p + 3, sfrml, chr(36))                             end if                         loop                         ws2                             .cells(rows.count, rvis.column).end(xlup).offset(1, 0).formula = sfrml                         end                     next rvis                 end if             end         end     end end sub 

of course, if had never intended transfer original worksheet's name along formula lot of code can discarded.