Results 1 to 3 of 3

Thread: Macro to cycle through dropdown, paste output to tab by grouping
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to cycle through dropdown, paste output to tab by grouping

    Hello,

    Hoping someone can point me towards a solution on the following:

    In tab "model", I have a model that outputs data in cells B2:G104. This is based on a dropdown selection in cell I5 that pulls from a list in tab "List by SL", cells B2:478.

    Cell I8 in the tab "model" is an index match formula that pulls in the grouping category for the dropdown list type.

    I need to automate the next part of the file, based on the following:


    • I'd like to create a tab for each grouping category in cell I8 from tab "model"
    • The macro should cycle through the dropdown in cell I5 in tab "model" and paste output from cells B2:G104 in tab "model" to the applicable grouping category tab
    • Since multiple outputs will be pasted to the same grouping category, it is important they are pasted below the previous output


    Any help or guidance is much appreciated - I'm having difficulty linking the pieces together but am continuing to research the steps to create.

    Thanks!

  2. #2
    New Member
    Join Date
    Dec 2015
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to cycle through dropdown, paste output to tab by grouping

    bump

  3. #3
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,757
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Macro to cycle through dropdown, paste output to tab by grouping

    What type of dropdown is it - a data validation in-cell dropdown, a combo box form control, or something else? The code to loop through the dropdown values is dfferent depending on the type.

    For example, this loops through data validation values:
    Code:
    Public Sub Loop_Data_Validation_Values()
    
        Dim dataValidationCell As Range, dataValidationListSource As Range, cell As Range
        Dim resp As Long
         
        'The data validation cell
        Set dataValidationCell = Worksheets("Model").Range("I2")
         
        'Determine where validation comes from
        Set dataValidationListSource = Evaluate(dataValidationCell.Validation.Formula1)
         
        'Loop through data validation source cells
        For Each cell In dataValidationListSource
            dataValidationCell.Value = cell.Value
            DoEvents
            If resp <> vbYes Then
                resp = MsgBox(dataValidationCell.Address & " = " & dataValidationCell.Value & vbCrLf & vbCrLf & "Continue? Yes - All, No - Next, Cancel - Stop", vbYesNoCancel)
                If resp = vbCancel Then Exit For
            End If
        Next
        
    End Sub
    Last edited by John_w; Jul 12th, 2019 at 12:52 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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