excel vba - Trouble accessing dual ranges -

i found majority of following code online , works awesome me. part have added creation of second range rnguniques2 , use of range string manipulation. problem having when try access range, not pulling correct value except first time. thinking using counter wrong, have not been able correct. know range has correct values in did each cell debug print.

sub extract_all_data()  'this macro assumes first row of data header row. 'will copy filtered rows 1 worksheet, blank workbook 'each unique filtered value copied it's own sheet  'variables used macro dim wborig, wbdest workbook dim rngfilter range, rnguniques, rnguniques2 range dim cell range, counter integer dim xvalue, outvalue string   ' prompt user choose file , open msgbox "please select file split." strfiletoopen = application.getopenfilename(title:="please select file split.", filefilter:="excel files *.xls* (*.xls*),")  if strfiletoopen = "false"     msgbox "no file selected.", vbexclamation, "sorry!"     exit sub else     set wborig = workbooks.open(filename:=strfiletoopen) end if   sheets("htpn").activate  ' set filter range (from a1 last used cell in column a) set rngfilter = range("a1", range("a" & rows.count).end(xlup))  application.screenupdating = false  rngfilter      ' filter column show 1 of each item (uniques) in column     .advancedfilter action:=xlfilterinplace, unique:=true      ' set variable unique values (one clientid , 1 client name)     set rnguniques = range("a2", range("a" & rows.count).end(xlup)).specialcells(xlcelltypevisible)     set rnguniques2 = range("b2", range("b" & rows.count).end(xlup)).specialcells(xlcelltypevisible)      ' clear filter     activesheet.showalldata  end  ' create new workbook sheet each unique value application.sheetsinnewworkbook = rnguniques.count set wbdest = workbooks.add application.sheetsinnewworkbook = 3  ' filter, copy, , paste each unique its' own sheet in new workbook each cell in rnguniques      counter = counter + 1      'note - filter on column (field:=1), change     'to different column need change field number     rngfilter.autofilter field:=1, criteria1:=cell.value      ' copy , paste filtered data it's unique sheet     rngfilter.resize(, 30).specialcells(xlcelltypevisible).copy destination:=wbdest.sheets(counter).range("a1")      ' name destination sheet     ' strip client name extract au #     xvalue = rnguniques2(counter, 1).value      debug.print xvalue      outvalue = ""      xindex = 1 vba.len(xvalue)         if (vba.mid(xvalue, xindex, 1) <> "-")                 if vba.isnumeric(vba.mid(xvalue, xindex, 1))                     outvalue = outvalue & vba.mid(xvalue, xindex, 1)                 end if         else: exit         end if     next      wbdest.sheets(counter).name = cell.value & " - " & outvalue     wbdest.sheets(counter).cells.columns.autofit  next cell  rngfilter.parent.autofiltermode = false application.screenupdating = true      end sub 

edit explain

sample data:

a b

1 a

1 a

1 a

2 b

2 b

3 c

3 c

3 c

3 c

4 d

4 d

4 d

the program copies rows of each unique item in column separate tabs , attempts name tab correctly. naming of tab causing me troubles. trying tab name in following format "a value - b value", above example, there 4 tabs named:

1 - a

2 - b

3 - c

4 - d

rnguniques contains unique values column , rnguniques2 contains corresponding values column b. trying read both ranges in same each loop, not accessing correct data in rnguniques2. example when run macro, name tabs:

1 - a

2 -

3 - b

4 - c

the small sample size above makes 1 item off, gets further off goes. actual data creates 110 separate tabs. assuming error comes way trying access data below.

xvalue = rnguniques2(counter, 1).value 

how progress through data of range while using each loop on different range?

okay thanks, last edit quite bit.

final clarification: if text in column always, "2", text in column b always, say, "b"? or there times when columna might 2 column b might "c"? because me, think overcomplicating things doing checking of rnguniques2 @ all.

assuming column b same each unique column value, can delete references rnguniques2 except initial "set = column b", , when setting name of sheet, go "rnguniques(counter,1).value & " - " & rnguniques(counter,2).value"

i can't see point of section of code here:

xvalue = rnguniques2(counter, 1).value  debug.print xvalue  outvalue = ""  xindex = 1 vba.len(xvalue)     if (vba.mid(xvalue, xindex, 1) <> "-")             if vba.isnumeric(vba.mid(xvalue, xindex, 1))                 outvalue = outvalue & vba.mid(xvalue, xindex, 1)             end if     else: exit     end if next