Copying results from dropdown to separate sheets

This question is to help a friend at work. Assume a workbook with a Main tab

On cell A3 is a dropdown where you can select up to 8 products which then limits the display on Main to that product.

Upper management asked that he create a separate tab for each view, i.e. Prod1 - Prod8 as separate sheets each showing the filtered data for that product.

He tried to record a macro, but cell A3 defaults to all products.

I assume he needs to create a loop based on the product names in the list range... any suggestions.

Quote:

---

Originally Posted by stanl

...On cell A3 is a dropdown where you can select up to 8 products which then limits the display on Main to that product.

---

Howdy "neighbor"

Presuming you mean Data Validation reference the dropdown, I am not understanding how you can select up to 8 products?

Sorry if I am just being thick-headed, but maybe a small workbook example (.xls format)?

Mark

Not at all... I didn't mean to imply selecting 8 options, but that 8 were available as a validation list. So, each time an option is selected, the data on the sheet changes to reflect that option. So the intent was to preserve that data as a separate sheet named for the option selected. I am used to creating separate sheets from a db table and using copyfromrecordset() so the approach used by my co-worker threw me off... but that what he wants to do.

Utterly not sure of the DV part (it's been a while and I have the memory of a gnat) but just as a first try, with the DV and the drop-down on the same sheet, maybe something like:

[vba]Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wks As Worksheet

If Target.Count = 1 And Target.Address(-1, -1, xlA1, 0) = "$A$1" Then
Me.Range(Me.Cells(2, 1), Me.Cells(Me.Rows.Count, 5)).AutoFilter 3, Me.Cells(1)
Me.Range(Me.Cells(2, 1), Me.Cells(Me.Rows.Count, 5)).SpecialCells(xlCellTypeVisible).Copy
Set wks = Application.Workbooks.Add(xlWBATWorksheet).Worksheets(1)
wks.Paste wks.Cells(1)
Application.CutCopyMode = False
Me.AutoFilterMode = False
End If
End Sub[/vba]

Again, just a first try, but am I at least picturing it right?

Mark