Macro to run a form control Drop down for all individual values in the dropdown

[surya444]

I need to run a excel table that dynamically changes based on form control drop down selection. I want to run a macro that changes the dropdown and copy+paste my table else where for all values in the form control.

Can someone please help ?


from the pic: based on brand selection, the table gets updated.


Thank you!

 

[John_w]

Try this macro, after changing the sheet names, drop down name, table and destination cell to suit.

As written, the dropdown is named "Drop Down 1" on the sheet named "Sheet1"; the table is the first table on "Sheet1"; the table is copied and pasted to cell A1 on the sheet named "Sheet3".

Public Sub Loop_Dropdown_Copy_Table_To_Other_Sheet()

    Dim brandDropdown As DropDown
    Dim table As ListObject
    Dim destCell As Range
    Dim i As Long
    
    Set brandDropdown = Worksheets("Sheet1").Shapes("Drop Down 1").OLEFormat.Object

    Set table = Worksheets("Sheet1").ListObjects(1)
    
    Set destCell = Worksheets("Sheet3").Range("A1")
    
    For i = 1 To brandDropdown.ListCount
        brandDropdown.ListIndex = i
        With destCell
            table.Range.Copy
            .PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    Next

    Application.CutCopyMode = False
    
End Sub

 

[surya444]

Hi John,

I made some changes in the code and it works 90% of the requirement
Except that the values are replaced by the last 'value' in drop-down. how can the code be modified to see that with every change in the drop down, the range is copied and pasted beneath the previous results?

Thank you,
Surya


Public Sub Loop_Dropdown_Copy_Table_Tther_Sheet()

Dim brandDropdown As DropDown
Dim strtcell As Range
Dim destCell As Range
Dim sht As Excel.Worksheet
Dim i As Long

Set brandDropdown = Worksheets("Brand - ADS Aug'23").Shapes("Drop Down 26").OLEFormat.Object

Set strtcell = Worksheets("Brand - ADS Aug'23").Range("E23:AG36")

For i = 1 To brandDropdown.ListCount
brandDropdown.ListIndex = i

Set destCell = Worksheets("Sheet10").Range("A1")


With destCell
strtcell.Copy
.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Next

Application.CutCopyMode = False

End Sub

 

[John_w]

Except that the values are replaced by the last 'value' in drop-down. how can the code be modified to see that with every change in the drop down, the range is copied and pasted beneath the previous results?

Add this before the End With:

        Set destCell = destCell.Offset(strtcell.Rows.Count + 1)