Hi,
I have a strange one. I wrote a macro that creates a table from data exported out of our MRP system and then summarizes it in a pivot table.
Everything works for me but when I watched another user try to use it, it created the table and the second sheet but the second sheet is empty. No error messages popped up. He hasn't used macros before - is there some setting in his Excel that needs to be turned on? We both run 32 bit versions of Excel 365. The code is only run on a fresh copy of the file and only contains Sheet1 with the raw data.
Here is the code:
Thanks for your help,
Russ
I have a strange one. I wrote a macro that creates a table from data exported out of our MRP system and then summarizes it in a pivot table.
Everything works for me but when I watched another user try to use it, it created the table and the second sheet but the second sheet is empty. No error messages popped up. He hasn't used macros before - is there some setting in his Excel that needs to be turned on? We both run 32 bit versions of Excel 365. The code is only run on a fresh copy of the file and only contains Sheet1 with the raw data.
Here is the code:
VBA Code:
Sub MB51_Create_Pivot_Table()
'
Dim x As Integer
On Error Resume Next
Application.CutCopyMode = False
Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)), , xlYes).Name _
= "Table1"
Range("Table1[#All]").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=7).CreatePivotTable TableDestination:="Sheet2!R3C1", _
TableName:="PivotTable1", DefaultVersion:=7
Sheets("Sheet2").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("Movement Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Plant")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Quantity"), "Sum of Quantity", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Movement Type")
For x = 101 To 1000
.PivotItems("" & x & "").Visible = False
Next
.PivotItems("261").Visible = True
.PivotItems("601").Visible = True
.PivotItems("Z21").Visible = False
End With
End Sub
Thanks for your help,
Russ