Friday, 27 June 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()

Pivot_Name = Selection.PivotTable.Name

'ActiveWorkbook.RefreshAll [ To Refresh all Pivots Data  in a WorkBook ]
End Sub


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

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.Add Key _
        :=Range(Sort_Range), SortOn:=xlSortOnValues, Order:=xlDescending, _
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range(MyRange)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
End Sub

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts