0

What commands should I input in order not needing to amend physically the sheet number when I run the pivot table macro and sheet name?

Dim pivotsheetname As String
Dim datasheetname As String
datasheetname = ActiveSheet.Name

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Orders!R1C1:R51291C24", Version:=6).CreatePivotTable TableDestination:="", TableName:="PivotTable1", DefaultVersion:=6

    Sheets("pivotsheetname").Select
    Sheets("pivotsheetname").Name = datasheetname & "Pivot"

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Country")
        .Orientation = xlRowField
        .Position = 1
    End With

    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Profit"), "Sum of Profit", xlSum
    Range("C8").Select

    Range("C18").Select
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add "Cost incurred", _
        "=SUM(Sales )-SUM(Profit )", True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Cost incurred"). _
        Orientation = xlDataField
    Range("B4:C5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "$#,##0.00"
    Range("D6").Select
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Shipping Cost"), "Sum of Shipping Cost", xlSum
    Range("E8").Select
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields.Add _
        "% of shipping cost", "=SUM('Shipping Cost' )/SUM('Cost incurred' )", True
    ActiveSheet.PivotTables("PivotTable1").PivotFields("% of shipping cost"). _
        Orientation = xlDataField
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Percent"
    Range("D7").Select
    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    Range("E9").Select
    Application.GenerateGetPivotData = True
    Application.GenerateGetPivotData = False
    Range("D5").Select


End Sub
Dominique
  • 16,450
  • 15
  • 56
  • 112
harold quek
  • 3
  • 5
  • 1
    Welcome to Stack Overflow. Please view this helpful resource: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Badja Mar 19 '19 at 11:51
  • When you say *"do not need to physically amend the Sheet No whenever I run the pivot table macro"*, do you mean "amend the Sheet Number"? The rows for the Source Data? Or the Sheet Name? Or do you mean "do not need to physically amend the Sheet whenever I run the pivot table macro" (with the "No" being a mistake)? As a general rule, when explaining an issue, you should assume we know *nothing* about your issue, and write it all out: What you want, what you expect, and what you are getting. That way, we get a [mcve] – Chronocidal Mar 19 '19 at 11:53
  • Hi Badja, what I meant was "do not need to physically amend the sheet whenever I run the pivot table macro". – harold quek Mar 20 '19 at 07:49

0 Answers0