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


New Member

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.



MrExcel MVP
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:
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
        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
End Sub
Last edited:

Some videos you may like

This Week's Hot Topics