none
Need to make the macro script dynamic RRS feed

  • Question

  • Dear All,

    I have the below macro script which was created when I had tried to record a macro.  Please help me in making this script more dynamic.  It includes Pivot, vlookup and this macro is intended to extract data for analysis

    Sub GM_Analysis()
    '
    ' GM_Analysis Macro
    '

    '
        Sheets("Revenue").Select
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Revenue!R1C1:R7838C57", Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="", TableName:="PivotTable1", DefaultVersion _
            :=xlPivotTableVersion15
            
            ActiveWorkbook.ShowPivotTableFieldList = True
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Amount INR AS PER BEACON EXCHANGE RATE"), _
            "Sum of Amount INR AS PER BEACON EXCHANGE RATE", xlSum
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("IOU")
            .Orientation = xlPageField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Group Customer")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveWorkbook.ShowPivotTableFieldList = False
        ActiveSheet.PivotTables("PivotTable1").PivotFields("IOU").ClearAllFilters
        ActiveSheet.PivotTables("PivotTable1").PivotFields("IOU").CurrentPage = _
            "NGM-APAC1-Parent"
        Sheets.Add After:=ActiveSheet
        
        Sheets("Sheet2").Name = "APAC GM Analysis"
        Sheets("Sheet1").Select
        Selection.Copy
        Range("A4").Select
        Range(Selection, Selection.End(xlDown)).Select
        Range("A4:A55").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet1").Select
        Range("A3:B56").Select
        Range("A4").Activate
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("APAC GM Analysis").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Rows("1:1").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Group Customer"
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "Revenue"
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "Cost"
        Range("C2").Select
        Sheets("Cost").Select
        Cells.Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Cost!R1C1:R1048576C61", Version:=xlPivotTableVersion15).CreatePivotTable _
            TableDestination:="", TableName:="PivotTable2", DefaultVersion _
            :=xlPivotTableVersion15
        Sheets("Sheet3").Select
        Cells(3, 1).Select
        ActiveWorkbook.ShowPivotTableFieldList = True
        ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
            "PivotTable2").PivotFields("Amount INR AS PER BEACON EXCHANGE RATE"), _
            "Count of Amount INR AS PER BEACON EXCHANGE RATE", xlCount
        With ActiveSheet.PivotTables("PivotTable2").PivotFields( _
            "Count of Amount INR AS PER BEACON EXCHANGE RATE")
            .Caption = "Sum of Amount INR AS PER BEACON EXCHANGE RATE"
            .Function = xlSum
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("IOU")
            .Orientation = xlPageField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable2").PivotFields("Group Customer")
            .Orientation = xlRowField
            .Position = 1
        End With
        ActiveWorkbook.ShowPivotTableFieldList = False
        ActiveSheet.PivotTables("PivotTable2").PivotFields("IOU").ClearAllFilters
        ActiveSheet.PivotTables("PivotTable2").PivotFields("IOU").CurrentPage = _
            "NGM-APAC1-Parent"
        Sheets("APAC GM Analysis").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet3!R[2]C[-2]:R[58]C[-1],2,0)"
        Range("C2").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet3!R4C1:R60C2,2,0)"
        Range("C2").Select
        Selection.Copy
        Range("B2").Select
        Selection.End(xlDown).Select
        Range("C54").Select
        Range(Selection, Selection.End(xlUp)).Select
        Range("C3:C54").Select
        Range("C54").Activate
        ActiveSheet.Paste
        Selection.End(xlUp).Select
        Range("B2:C2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Application.CutCopyMode = False
        Selection.Style = "Comma"
        Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
        Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
        Range("B3").Select
        Selection.End(xlToLeft).Select
        Range("A4").Select
        Selection.End(xlDown).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.ClearContents
        Range("A52").Select
        Selection.End(xlUp).Select
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "GM (%)"
        Range("D2").Select
        ActiveCell.FormulaR1C1 = "=IFERROR((RC[-2]-RC[-1])/RC[-2],"""")"
        Range("D2").Select
        Selection.Style = "Percent"
        Range("D2").Select
        Selection.Copy
        Range("C2").Select
        Selection.End(xlDown).Select
        Range("D53").Select
        Range(Selection, Selection.End(xlUp)).Select
        Range("D3:D53").Select
        Range("D53").Activate
        ActiveSheet.Paste
        Selection.End(xlUp).Select
        Range("A1:D1").Select
        Application.CutCopyMode = False
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With
        Selection.Font.Bold = True
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Range("A1:D1").Select
        Range("B1").Activate
        ActiveWorkbook.Save
        Sheets(Array("Sheet1", "Sheet3")).Select
        Sheets("Sheet3").Activate
        ActiveWindow.SelectedSheets.Delete
        Range("B10").Select
        ActiveWindow.SmallScroll Down:=-12
        ActiveWorkbook.Save
    End Sub

    • Moved by Bill_Stewart Wednesday, November 16, 2016 3:53 PM Move to more appropriate forum
    Wednesday, November 16, 2016 9:49 AM

All replies

  • Sorry but you are posting in the wrong forum.  A macro is VBA and not a script.  Post in Excel forum.


    \_(ツ)_/

    Wednesday, November 16, 2016 10:22 AM
  • Hi,

    How do you want it become dynamic? Do you want to use variable to refer the sheet, PivotTable, or the value to search?

    I think you could Dim several variables, use MsgBox Function to accept and then pass.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 18, 2016 2:52 AM
    Moderator