Consulting

Results 1 to 4 of 4

Thread: Copying results from dropdown to separate sheets

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    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.

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    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

  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    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.

  4. #4
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •