i looking model can copy particular range in excel sheet new sheet. have 5 columns. first column year. want indicate, example inputboxwhich year should copied. in column b, weeks of year indicated. in columns c until e, specific data recorded. result, sheet this:
col col b col c col d cole year week amount time forecast 2000 1 368 2000w1 400 2000 2 8646 2000w2 8500 until... 2014 52 46546 2014w52 47000 with input box, want indicate year 2014 should copied next sheet. until wrote/compiled following macro:
sub copy_year() dim forecastyear string dim rng range forecastyear = inputbox("enter year forecast") if trim(forecastyear) <> "" sheets(2).range("a:a") set rng = .find(what:=forecastyear, _ after:=.cells(.cells.count), _ lookin:=xlvalues, _ lookat:=xlwhole, _ searchorder:=xlbyrows, _ searchdirection:=xlnext, _ matchcase:=false) if not rng nothing application.goto rng, true else msgbox "nothing found" end if end end if 'range(activecell, rc[52,4]).select end sub this select first cell contains year value. want select full 5 columns row selected cell , next 51 weeks (together 52 weeks). how can select data?
use this
sub copy_year() dim forecastyear string dim rng range forecastyear = inputbox("enter year forecast") if trim(forecastyear) <> "" sheets(2) each cell in .range("a:a") if cell.value = forecastyear then'find first occurrence of year set rng = cell exit end if next .range(rng.address).resize(52, 5).select'resize 52 rows , 5 columns end end if end sub what going now? selecting rather inefficient in terms of cpu time
Comments
Post a Comment