Friday, June 27, 2014

How to Refresh a Pivot Table with Excel VBA Macro

Macro to Refresh a Pivot Table existed at a Dynamic Active Cell
'A Simple macro to refresh a Pivot Table whose name not know in advance , by Dynamically based on Active Cell
Sub Pvt_Refresh()

ActiveSheet.Range("I4").Select
Pivot_Name = Selection.PivotTable.Name

ActiveSheet.PivotTables(Pivot_Name).PivotCache.Refresh
'ActiveWorkbook.RefreshAll [ To Refresh all Pivots Data  in a WorkBook ]
End Sub





Thanks.,TAMATAM

How to do Advanced Sorting the Desired Selected Range Dynamically in Excel

Dynamic Macro for Advanced Sorting the Desired Selected Range
This Macro is very Dynamic used for Advanced Sorting the Desired Selected Range by Desired Column.

Sub Adv_Sorting()

MyRange = Selection.Address
First_Row = Selection.Row
Rows_Selected = Selection.Rows.Count
Last_Row = (Selection.Rows.Count + Selection.Row) - 1

SC:
Sort_Column = InputBox("Please Enter Column by Which You Want to Sort" & vbNewLine _
& "Eg: A , B , C ...", "Please Enter Advanced Sorting Column")

'[To Exit Macro On Clicking 'Cancel' of Input box]
If Sort_Column = vbNullString Then Exit Sub 

X = UCase(Sort_Column)
Sort_Range = "$" & X & "$" & First_Row & " : " & "$" & X & "$" & Last_Row

If X = "" Or Len(X) > 1 Then

MsgBox "Please Give Column Name as : A , B, C ...", vbOKOnly, "Try Again"
GoTo SC:
End If

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key _
        :=Range(Sort_Range), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range(MyRange)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog