match - VBA; Lookup multiple instances of a variable and copy range to another sheet -

i have spreadsheet tab containing amounts paid various clients. there more 1 row per client. need able select instances of client, copy columns of data each instance of client tab. @ moment know how select first instance of record.

so example, want put instances of client id 1 tab;

data tab

id amt

1 £20

2 £10

3 £15

1 £10

2 £20

invoice tab

id amt

1 £20

1 £10

i hope i've explained enough, please let me know if need more detail. i'm pretty new vba sorry if easy question.

thanks in advance :)

eta

i tried adapt piece of code found, failed work @ all;

'what value want find (must in string form)?  fnd = 1  set myrange = worksheets("data").range("i:ac") set lastcell = worksheets("data").cells(myrange.cells.count)  set foundcell = myrange.find(what:=fnd, after:=lastcell)  'test see if found   if not foundcell nothing     firstfound = foundcell.address   else     goto nothingfound   end if  set rng = foundcell  'loop until cycled through unique finds   until foundcell nothing     'find next cell fnd value       set foundcell = myrange.findnext(after:=foundcell)      'add found cell rng range variable       set rng = union(rng, foundcell)      'test see if cycled through first found cell      if foundcell.address = firstfound exit    loop  'select cells containing find value rng.select  exit sub 

please try this. create multiple sheets each client. should able change behaviour if required, once go through code obvious

sub makeinvoicesheets()      dim ws worksheet, strstartcol string, strendcol string, istartrow integer, iendrow integer     dim iclient string, wsinv worksheet, strcompletedclients string      istartrow = 1     iendrow = 8     strstartcol = "a"     strendcol = "b"      set ws = sheets("client")     strcompletedclients = ","      irow = istartrow + 1 iendrow         iclient = ws.range(strstartcol & irow).text          if instr(1, strcompletedclients, "," & iclient & ",") <= 0             ws.select             ws.range("$" & strstartcol & "$" & istartrow & ":$" & strendcol & "$" & iendrow).autofilter field:=1, criteria1:="=" & iclient             ws.range(strstartcol & istartrow).select             ws.range(selection, activecell.specialcells(xllastcell)).select             selection.copy             set wsinv = sheets.add(after:=sheets(sheets.count))             wsinv.name = "invoice client " & iclient             wsinv.paste             ws.autofiltermode = false             strcompletedclients = strcompletedclients & iclient & ","         end if     next  end sub