excel - How to make selection move through variable data in VBA -

i trying continue process until end of cells, selection needs change in set order. here's code:

' c_p macro ' copies worksheet & pastes in new sheet ' keyboard shortcut: ctrl+shift+l    dim rng1 range   dim string   dim irow integer   windows("audubon-crest-rent-roll-201502-1.xlsx").activate   set rng1 = range("a:a")   activecell = range("a228")       windows("audubon-crest-rent-roll-201502-1.xlsx").activate     range("a228:a239").select         selection.copy     windows("audubon crest rent roll 20150219-altered.xlsm").activate     range("b17").select     selection.pastespecial paste:=xlpasteall, operation:=xlnone, skipblanks:= _         false, transpose:=true     windows("audubon-crest-rent-roll-201502-1.xlsx").activate     application.cutcopymode = false      range("a228:a239").offset(13, 0).select     selection.copy     windows("audubon crest rent roll 20150219-altered.xlsm").activate     range("b17").offset(1, 0).select     selection.pastespecial paste:=xlpasteall, operation:=xlnone, skipblanks:= _         false, transpose:=true     range("b18").select     loop until isempty(cells(irow, 1)) end end sub 

as can see, make selection of 11 rows , transpose copies data next workbook @ next row. f8'd through procedure , can tell works 1 selection moves down 13 rows , next selection, i'm not sure how make continuously move through data. need select 11 rows, procedure count thirteen rows down , again until hits empty row. ideally set find cell entry , select 11 rows once locates next instance of cell entry, think 13 rows remains pattern if counts down 13 rows should fine.

so, think quandary how make row selections variable. when code can see row selections concrete values if run macro wouldn't move down, cells have indicated. how can make move down through cells?

you don't need activate workbook range in it, rather can range in non-active workbook qualifying workbook range in first. same applies worksheets , ranges. admittedly not causing problems in code-sample posting, since you're not touching on that, making code lot harder read.

one possible solution problem macro looking this:

sub copy_paste     dim sourcebook workbook, destbook workbook, sourcerange range, destrange range      set sourcebook = workbooks("audubon-crest-rent-roll-201502-1.xlsx")     set destbook = workbooks("audubon crest rent roll 20150219-altered.xlsm")      set sourcerange = sourcebook.worksheets(<name of sheet data in string>).range("a228:a239")     set destrange = destbook.worksheets(<name of sheet data shall copied string>).range("b17")              sourcerange.copy         destrange.pastespecial paste:=xlpasteall, operation:=xlnone, skipblanks:=false, transpose:=true         application.cutcopymode = false          set destrange = destrange.offset(1, 0)         set sourcerange = sourcerange.offset(13, 0)     loop until isempty(sourcerange) end sub 

as can see define ranges shall copied, naming them , telling excel using set statement. each iteration of loop move destination range 1 row further down setting again, , letting offset 1 row previous destination range, using offset-property. same source-range, offsetting 13 rows, looked doing in code-sample.

i hope helps!