excel vba - How to select a range of rows using two variables in VBA -

i'm brand new vba , trying write macro pull specific data workbooks in same directory master workbook. i'm getting stuck when trying select range of data using variables can copy , paste data master workbook. i've been watching videos , reading through forums, can't seem macro work.

i have excel sheet lists employees in column a, data want copy employees in columns b, c, d, e, , f (in subsequent rows). example, row 1 contains first employee in cell a1, , rows 2 through 5 contains data in columns b through f. row 6 contains next employee's name in cell a6, , data them resides in rows 7 through 9 (columns b-f). want copy rows 2-5 , paste them master workbook, , copy 7-9 , paste master, 8-14, , on , forth.

my first attempt define 2 variables integers. tried find name of first employee in column , select row after, , set first variable equal row. find name of second employee, select row before , set variable 2 equal row. select range using 2 variables. here's code looks like:

sub selectconsultantdata() dim consultant1 integer, consultant2 integer dim consultantrange range      columns("a:a").select     selection.find(what:="andrew", after:=activecell, lookin:=xlvalues, _         lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _         matchcase:=false, searchformat:=false).activate     consultant1 = rows(activecell.row).select     consultant1 = consultant1 + 1     columns("a:a").select     selection.find(what:="bob", after:=activecell, lookin:=xlvalues, _         lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _         matchcase:=false, searchformat:=false).activate     consultant2 = rows(activecell.row).select     consultant2 = consultant2 - 1     set consultantrange = range(consultant1, consultant2).select  end sub 

any idea i'm doing wrong, or can think of better approach? please let me know if need provide further context.

thanks in advance help.

your code can re-written below. avoid using select in code. check link know why.

sub selectconsultantdata()     dim consultant1 integer, consultant2 integer     dim consultantrange range      dim rngfind range     set rngfind = columns("a:a").find(what:="andrew", after:=range("a1"), lookin:=xlvalues, lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext)      if not rngfind nothing         consultant1 = rngfind.row + 1     end if      set rngfind = columns("a:a").find(what:="bob", after:=range("a1"), lookin:=xlvalues, lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext)      if not rngfind nothing         consultant2 = rngfind.row - 1     end if      if consultant1 > 0 , consultant2 > 0         set consultantrange = range(cells(consultant1, 2), cells(consultant2, 6))     end if  end sub