Thread: Run macro with value based on button clicked

Hi All,



I have like three different buttons which perform the same functions. but the values they deal with are from three different pivot tables.



For eg, When button 1 is clicked, it takes values from Pivot table 1 and executes the functionalities of the macro. When button 2 is clicked, values from Pivot Table 2 are taken and the SAME macro is executed.



So essentially, the macro is the same, but it has to retrieve information from respective pivot table based on button clicked. I would like to know as to how to get this done.



Also, if there is a way that I can pass values from one macro to another, I guess I can get this done. My approach was to provide an table number to each of the pivot table and pass that number to the Macro to choose that pivot table and execute from there.



Let me know if there is a better way to do.

Provided is the code which copies the pivot table and pastes it in another sheet. If you look at the name of the pivot table, it says "Pivot_yearly"&i - which means 'i' is the integer specifying the pivot table number.

The number is to be allocated based on the button pressed. If button 1 is pressed, value of i is 1, if button 2 is pressed, value of i is 2 and so on.

Hope its clear.

On Error GoTo Reset

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Dim my_range As Range
    Worksheets("Pivot").Cells.Delete
    Worksheets("PivotTables").Activate
    Worksheets("PivotTables").PivotTables("Pivot_Yearly_" & i).PivotSelect "", xlDataOnly, True
    With Selection
        Set my_range = .Offset(, -1).Resize(.Rows.count - 1, .Columns.count + 1)
    End With
    my_range.Copy
    Worksheets("Pivot").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
Reset:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True

something like ...........

Sub BtnClickedWotsit()
    ' assign this macro to the buttons
    Dim i As Integer
    
    ' name buttons with names as below or change to suit
    Select Case Application.Caller
        Case "Button_1_Name"
            PivotTableWotsit (1)
        Case "Button_2_Name"
            PivotTableWotsit (2)
        Case "Button_3_Name"
            PivotTableWotsit (3)
        Case Else
            MsgBox "I wasn't expecting a call from this button"
            Exit Sub
    End Select
    
End Sub

Sub PivotTableWotsit(i As Integer)
    Dim my_range As Range
    
    On Error GoTo Reset
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    Worksheets("Pivot").Cells.Delete
    With Worksheets("PivotTables")
        .Activate
        .PivotTables("Pivot_Yearly_" & i).PivotSelect "", xlDataOnly, True
    End With
    With Selection
        Set my_range = .Offset(, -1).Resize(.Rows.Count - 1, .Columns.Count + 1)
    End With
    my_range.Copy
    Worksheets("Pivot").Range("A1").PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    
Reset:
    Set my_range = Nothing
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub