Excel VBA - Loop to filter table for each value in column and paste in according worksheet -

i trying filter table on first worksheet ("data") each of items appear in table on second worksheet ("hosts"), , paste filtered results in separate worksheets, each named after corresponding item on table.

my understanding of vba basic , have tried put collage of codes other users, doesn't seem work me:

• the first loop creates worksheets based on items on "hosts" table, reason adds sheet before ones need , calls "sheet1"
• the second loop doesn't work
• are 2 loops necessary, or possible combine two?

this code have far:

sub test() dim alldata worksheet dim hostlist worksheet dim datarange range dim filtercolumn long  set alldata = thisworkbook.worksheets("data") set hostlist = thisworkbook.worksheets("hosts") set datarange = alldata.range(range("a1"), range("a1").specialcells(xllastcell))  dim hostvalues range  each hostvalues in hostlist.listobjects("table1").range      thisworkbook.sheets.add(after:=thisworkbook.sheets(thisworkbook.sheets.count))         on error resume next         activesheet.name = hostvalues.value         if err.number = 1004             debug.print hostvalues.value & "already used sheet name"         end if         on error goto 0     end  next hostvalues  each hostvalues in hostlist.listobjects("table1").range     alldata.activate     filtercolumn = 18     datarange.autofilter field:=filtercolumn, criteria1:=hostvalues         datarange.specialcells(xlcelltypevisible).copy      sheets(hostvalues.text).range("a1").pastespecial xlpastevalues     selection.sort key1:=range("v:v"), order1:=xlascending, header:=xlguess      alldata.activate     cells.autofilter next hostvalues end sub 

some kind soul out there please me!

there's quite bit here, i'll give shot.

the first loop creates worksheets based on items on "hosts" table, reason adds sheet before ones need , calls "sheet1"

my guess here hosts contains duplicate entry or causing sheet rename section fail. check debug window that. or change

debug.print hostvalues.value & "already used sheet name" 

to

msgbox hostvalues.value & "already used sheet name" 

that make popup happen, should make easier see when error happens. else can try, comment out 2 onerror statements ' single quote. when error raised can hit debug , work through program upset with.

the second loop doesn't work

i'm not sure on one. when use for each many times changing collection operating on give sort of problem. you've asked computer every cell in column, , change values of column. that's guess.

are 2 loops necessary, or possible combine two?

you can combine two, after creating sheet host can move data on it.

notes

• the approach filtering may giving undue complexity try writing loop without filters , checking if host has sheet, if move data. if not create , move data.
• you not need with/end with block @ all.
• on error resume next dangerous. has uses, take @ this more information or handling errors.

godspeed.