keerthimalathkar
New Member
- Joined
- May 7, 2024
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Sub GoLive()
'
' GoLive Macro
'
'
ActiveCell.FormulaR1C1 = "College"
Range("A1:M56417").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DSI RFI Activity Summary Since !R1C1:R56417C13", Version:=8). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=8
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Tracking Code: Tracking Code")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Tracking Code: Tracking Code").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Tracking Code: Tracking Code").CurrentPage = "INQ-WEBFORM-UNTONLINE"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("College")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Program")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Tracking Date"), "Count of Tracking Date", xlCount
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "UNT Online RFIs"
Range("A27").Select
Sheets("DSI RFI Activity Summary Since ").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.Worksheets("UNT Online RFIs").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:= _
"PivotTable2", DefaultVersion:=8
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable2").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$3:$C$20")
With ActiveChart.PivotLayout.PivotTable.PivotFields( _
"Tracking Code: Tracking Code")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Tracking Code: Tracking Code").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Tracking Code: Tracking Code").CurrentPage = "INQ-WEBFORM-UNTONLINE"
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("College")
.Orientation = xlPageField
.Position = 1
End With
With ActiveChart.PivotLayout.PivotTable.PivotFields("Program")
.Orientation = xlPageField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("Tracking Date"), "Count of Tracking Date", xlCount
With ActiveChart.PivotLayout.PivotTable.PivotFields("Tracking Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTables("PivotTable2").PivotFields("Years").Orientation = xlHidden
ActiveChart.PivotLayout.PivotTables("PivotTable2").PivotFields("Quarters").Orientation = xlHidden
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -377.25
ActiveSheet.Shapes("Chart 1").IncrementTop -156.75
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "By Date"
Range("E33").Select
ActiveWorkbook.Save
End Sub
'
' GoLive Macro
'
'
ActiveCell.FormulaR1C1 = "College"
Range("A1:M56417").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DSI RFI Activity Summary Since !R1C1:R56417C13", Version:=8). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=8
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Tracking Code: Tracking Code")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Tracking Code: Tracking Code").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Tracking Code: Tracking Code").CurrentPage = "INQ-WEBFORM-UNTONLINE"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("College")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Program")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Tracking Date"), "Count of Tracking Date", xlCount
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "UNT Online RFIs"
Range("A27").Select
Sheets("DSI RFI Activity Summary Since ").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.Worksheets("UNT Online RFIs").PivotTables("PivotTable1"). _
PivotCache.CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:= _
"PivotTable2", DefaultVersion:=8
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable2").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$3:$C$20")
With ActiveChart.PivotLayout.PivotTable.PivotFields( _
"Tracking Code: Tracking Code")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Tracking Code: Tracking Code").ClearAllFilters
ActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Tracking Code: Tracking Code").CurrentPage = "INQ-WEBFORM-UNTONLINE"
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.PivotLayout.PivotTable.PivotFields("College")
.Orientation = xlPageField
.Position = 1
End With
With ActiveChart.PivotLayout.PivotTable.PivotFields("Program")
.Orientation = xlPageField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("Tracking Date"), "Count of Tracking Date", xlCount
With ActiveChart.PivotLayout.PivotTable.PivotFields("Tracking Date")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.PivotLayout.PivotTables("PivotTable2").PivotFields("Years").Orientation = xlHidden
ActiveChart.PivotLayout.PivotTables("PivotTable2").PivotFields("Quarters").Orientation = xlHidden
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -377.25
ActiveSheet.Shapes("Chart 1").IncrementTop -156.75
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "By Date"
Range("E33").Select
ActiveWorkbook.Save
End Sub