AdvancedFilter method with workbooks

[shauntwo]

Hey,

I have a spreadsheet of data grouped by customers, and what I'd like to do is copy each grouping of customer into a separate workbook. However, some groupings might need to be saved as two different workbooks. So, I've set up a range list of all unique customers along with an adjancent cell which shows the file name to which that particular customer group should be saved. As an example, here's the unqiue list:

NAME FILE
Customer1 1Cust1
Customer1 2Cust1
Customer2 1Cust2

... and so on. How should I go about doing this? I'm trying to use the Range.AdvancedFilter Action:=xlFilterCopy to do this, but I don't know the syntax to use to copy to a separate workbook. Also, I don't know how to reference the adjancent cell of a range to use in this case, so that I can just loop through the unique name list. Any help would be much appreciated.

Shaun

 

[xlbo]

I guess Dale'd be the one to ask about Advanced filter...but he's on hols. On teh 2nd question, use
Activecell.offset(0,1).text to reference youradjacent column of data - OFFSET(RowIndex,ColIndex)
Rgds
~Geoff~

 

[shauntwo]

Thanks Geoff - actually, I'm trying to customize a file that Dale mentioned on here for my needs. Thanks for addressing my second question; I'll be sure to use that.

Shaun

 

[xlbo]

Have you tried something like:
Destination:= Workbooks("MyCopyToWorkbook.xls&quot.sheets("SheetToCopyTo&quot.Range("A1&quot
?????
If I recall, you may need to set up a range name in your copyTo workbook for Advanced filter to copy off the active sheet so, something like
Destination:= Workbooks("MyCopyToWorkbook.xls&quot.range("MyRange&quot

I'm presuming that the workbook is open at the point you are trying to copy across 'cos you won't be able to do it with a closed one... Rgds
~Geoff~