Hello,
I have a shared macro enable excel workbook where the code runs perfectlyfine in my computer, but when another coworker runs the same macro using the sameworkbook, they get an error message when it comes to creating the pivot table(in red below). Why would it work perfectly fine in my computer, but not workin someone else’s computer? There are two pivot tables to be inserted and I amassuming I will get the same error for both pivot tables.
Thank you
I have a shared macro enable excel workbook where the code runs perfectlyfine in my computer, but when another coworker runs the same macro using the sameworkbook, they get an error message when it comes to creating the pivot table(in red below). Why would it work perfectly fine in my computer, but not workin someone else’s computer? There are two pivot tables to be inserted and I amassuming I will get the same error for both pivot tables.
Rich (BB code):
Option Explicit
Sub Watchlist()
'
'Watchlist Macro
'Created by Miriam Hamid on 5/29/18
'Updated by Miriam Hamid on 7/11/18
'Clear Prior Data
Dim lRow2 As Long
lRow2 = Range("AY" & Rows.Count).End(xlUp).Row
Columns("BF:BX").Select
Selection.DeleteShift:=xlToLeft
Selection.InsertShift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("AY5:BC"& lRow2 + 1).Select
Selection.Clear
'Declare Variables to hold source cell range address
Dim mySourceData As String
'Declare Variables to hold references to source and destinationworksheets and Pivot Table
Dim SourceSheet As Worksheet
Dim DestSheet As Worksheet
'Declare Variales to hold row and column numbers that will definesource data cell range
Dim FirstRow As Long
Dim lastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
'Set/Define Source and Destination Variables
With ThisWorkbook
Set SourceSheet =.Worksheets("Audit_Plan")
Set DestSheet =.Worksheets("OTRC MOR File")
End With
'identify first row and first column of source data cell range
FirstRow = 6
FirstCol = 1
'Find last row and last column of source data cell range
'And obtain address of source data cell range
With SourceSheet.Cells
lastRow =.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart,SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol =.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart,SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
mySourceData =.Range(.Cells(FirstRow, FirstCol), .Cells(lastRow,LastCol)).Address(ReferenceStyle:=xlR1C1)
End With
'Insert Pivot Table ("Audit_Plan!R6C1:R1048576C93")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
SourceSheet.Name & "!" &mySourceData, Version:=6).CreatePivotTable TableDestination _
:=DestSheet.Cells(4, 58), TableName:="PivotTable1",DefaultVersion:=6
'Insert Watch List Pivot Data
WithActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area")
.Orientation =xlPageField
.Position = 1
End With
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Change_Type")
.Orientation =xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_To")
.Orientation = xlRowField
.Position = 1
End With
WithActiveSheet.PivotTables("PivotTable1").PivotFields("L2_Business")
.Orientation = xlRowField
.Position = 2
End With
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Audit_Name")
.Orientation = xlRowField
.Position = 3
End With
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Observations")
.Orientation = xlRowField
.Position = 4
End With
WithActiveSheet.PivotTables("PivotTable1").PivotFields("L3_Region")
.Orientation = xlRowField
.Position = 5
End With
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Entity_Ownership")
.Orientation = xlRowField
.Position = 6
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Entity_Ownership").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Entity_Ownership")
.LayoutForm = xlTabular
.RepeatLabels = True
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("L3_Region").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable1").PivotFields("L3_Region")
.LayoutForm = xlTabular
.RepeatLabels = True
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Observations").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Observations")
.LayoutForm = xlTabular
.RepeatLabels = True
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Audit_Name").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Audit_Name")
.LayoutForm = xlTabular
.RepeatLabels = True
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("L2_Business").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable1").PivotFields("L2_Business")
.LayoutForm = xlTabular
.RepeatLabels = True
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Report_To").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable1").PivotFields("Report_To")
.LayoutForm = xlTabular
.RepeatLabels = True
End With
Range("BF4").Select
ActiveSheet.PivotTables("PivotTable1").RowAxisLayoutxlTabularRow
Columns("BF:BF").ColumnWidth = 25
Columns("BH:BH").Select
Selection.ColumnWidth = 25
ActiveSheet.PivotTables("PivotTable1").PivotSelect"Observations[All]", _
xlLabelOnly, True
Columns("BI:BI").Select
Selection.ColumnWidth = 25
Range("BA11:BQ11").Select
Range("BQ11").Activate
ActiveSheet.PivotTables("PivotTable1").PivotSelect _
"ASPAC 'Anne-MareeTassell' 'Multi O&T Businesses' 'A18338-APRA Prudential Standard CPS 232Business Continuity Management - Australia' Observations['(blank)']" _
, xlDataAndLabel, True
Range("BK16").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area").CurrentPage= _
"(All)"
WithActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area")
.PivotItems("Business").Visible = False
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("L1_Area")._
EnableMultiplePageItems =True
ActiveSheet.PivotTables("PivotTable1").PivotFields("Change_Type")._
ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Change_Type").CurrentPage_
= "Watchlist"
'Add Prior Month Date
Range("BN1").Select
With Selection
.FormulaR1C1 ="=EOMONTH(TODAY(),-1)-DAY(EOMONTH(TODAY(),-1))+1"
.NumberFormat ="mm/dd/yyyy"
.Interior.Pattern =xlSolid
.Interior.PatternColorIndex= xlAutomatic
.Interior.Color =65535
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
End With
Range("BN1").Copy
Selection.PasteSpecialPaste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode =False
'Insert Pivot Table ("Audit_Plan!R6C1:R1048576C93")
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
SourceSheet.Name & "!" & mySourceData,Version:=6).CreatePivotTable TableDestination _
:=DestSheet.Cells(4, 67), TableName:="PivotTable2",DefaultVersion:=6
'Insert Month End and After Insufficient/Limited Assurance Audits PivotData
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Report_To")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_To").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Business_Impacted")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Business_Impacted").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Audit_Name")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Audit_Name").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Observations")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Observations").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Region_Revised")
.Orientation = xlRowField
.Position = 5
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Region_Revised").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
WithActiveSheet.PivotTables("PivotTable2").PivotFields( _
"Report_Publication_Date")
.Orientation = xlRowField
.Position = 6
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")
ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")._
Subtotals = Array(False,False, False, False, False, False, False, False, False, False, _
False, False)
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Entity_Ownership")
.Orientation = xlRowField
.Position = 7
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Control_Rating")
.Orientation = xlRowField
.Position = 8
End With
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Total_Issues")
.Orientation = xlRowField
.Position = 9
End With
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Published_Report_Name"_
)
.Orientation = xlRowField
.Position = 10
End With
ActiveSheet.PivotTables("PivotTable2").RowAxisLayoutxlTabularRow
ActiveSheet.PivotTables("PivotTable2").PivotFields("Entity_Ownership")._
Subtotals = Array(False,False, False, False, False, False, False, False, False, False, _
False, False)
ActiveSheet.PivotTables("PivotTable2").PivotFields("Control_Rating").Subtotals_
= Array(False, False,False, False, False, False, False, False, False, False, False, False _
)
ActiveSheet.PivotTables("PivotTable2").PivotFields("Total_Issues").Subtotals= _
Array(False, False,False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable2").PivotFields("Published_Report_Name")._
Subtotals = Array(False,False, False, False, False, False, False, False, False, False, _
False, False)
WithActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area")
.Orientation =xlPageField
.Position = 1
End With
Columns("BO:BO").Select
Selection.ColumnWidth = 25
Columns("BX:BX").Select
Selection.ColumnWidth = 25
Range("BM14:CB19").Select
Range("CB19").Activate
Columns("BT:BT").ColumnWidth = 14
Columns("BU:BU").ColumnWidth = 11.38
ActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area").CurrentPage= _
"(All)"
WithActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area")
.PivotItems("Business").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("L1_Area")._
EnableMultiplePageItems =True
Columns("BP:BP").ColumnWidth= 15.25
Range("BT8").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")._
PivotItems("3/21/2018").ShowDetail = False
Columns("BT:BT").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Report_Publication_Date")._
PivotFilters.Add2Type:=xlAfterOrEqualTo, Value1:=Range("BN1")
Range("BV3").Select
WithActiveSheet.PivotTables("PivotTable2").PivotFields("Control_Rating")
.PivotItems("NotRated").Visible = False
.PivotItems("Roomfor Improvement").Visible = False
.PivotItems("Sufficient Assurance").Visible = False
.PivotItems("(blank)").Visible = False
End With
Columns("BV:BV").ColumnWidth = 19.38
Columns("BW:BW").EntireColumn.AutoFit
Columns("BV:BV").EntireColumn.AutoFit
Columns("BU:BU").EntireColumn.AutoFit
Columns("BS:BS").ColumnWidth = 13.5
Columns("BQ:BQ").ColumnWidth = 19.38
'Copy Data from 1st PivotTable into predefined Table and copy untillast row
Dim lRow As Long
lRow = Range("BJ" & Rows.Count).End(xlUp).Row
Sheets("OTRC MORFile").Select
Range("AY5").FormulaR1C1 ="=IF(OR(RC[7]="""",RC[7]=""(blank)""),""TBD"",RC[7])"
Range("AZ5").FormulaR1C1 = "=RC[7]&"" /""&RC[10]"
Range("BA5").FormulaR1C1 = "=RC[7]"
Range("BB5").FormulaR1C1 = "=""""&CHAR(149)&""""&RC[7]"
Range("BC5").FormulaR1C1 = "=RC[8]"
Range("AY5:BB5").Copy Range("AY" & lRow)
Application.CutCopyMode =False
'Copy Table Data and Paste Special Value
Range("AY5:BC20").Copy
Range("AY5:BC20").PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode =False
'Copy Data from 2nd PivotTable into predefined Table and copy untillast row
Dim LR As Long
Dim lRow3 As Long
lRow3 = Range("BX" & Rows.Count).End(xlUp).Offset(1).Row
LR = Range("AY" & Rows.Count).End(xlUp).Row
Range("AY" & LR+ 1 & ":AY" & lRow3).formula ="=IF(BO5=""(blank)"",""TBD"",BO5)"
Range("AZ" & LR+ 1 & ":AZ" & lRow3).formula = "=BP5&"" /""&BS5"
Range("BA" & LR+ 1 & ":BA" & lRow3).formula ="=IF(BX5=""(blank)"",BQ5,BX5)"
Range("BB" & LR+ 1 & ":BB" & lRow3).formula = "=""•""&""Published on""&TEXT(BT5,""mm/dd/yyyy"")&""with ""&BV5&"" rating and ""&BW5&"" issues:"""
Range("BC" & LR+ 1 & ":BC" & lRow3).formula = "=BU5"
'Copy Table Data and Paste Special Value
Range("AY5:BC20").Copy
Range("AY5:BC20").PasteSpecial Paste:=xlPasteValues,Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode =False
'Macro to replace "(i*" with bullets
Dim Bullet As String
Dim Bullet2 As String
'Define Variables
Bullet = " •"
'Replace roman numerals with bullets
Range("BB:BB").Select
Selection.ReplaceWhat:="(i*)", Replacement:=Bullet, LookAt:=xlPart, _
SearchOrder:=xlByRows,MatchCase:=False
Selection.ReplaceWhat:="(v*)", Replacement:=Bullet, LookAt:=xlPart, _
SearchOrder:=xlByRows,MatchCase:=False
Selection.ReplaceWhat:="(x*)", Replacement:=Bullet, LookAt:=xlPart, _
SearchOrder:=xlByRows,MatchCase:=False
Selection.ReplaceWhat:="(xv*)", Replacement:=Bullet, LookAt:=xlPart, _
SearchOrder:=xlByRows,MatchCase:=False
'Sort Column BC
Columns("AY:BC").Select
ActiveWorkbook.Worksheets("OTRC MORFile").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("OTRCMOR File").Sort.SortFields.Add Key:=Range("BC5:BC20" _
),SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
WithActiveWorkbook.Worksheets("OTRC MOR File").Sort
.SetRangeRange("BC5:BC20")
.Header = xlNo
.MatchCase = False
.Orientation =xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Header Creation and Formatting
Range("AY4").FormulaR1C1 = "Responsible Owner"
Range("AZ4").FormulaR1C1 = "Business / Region"
Range("BA4").FormulaR1C1 = "Review Name"
Range("BB4").FormulaR1C1 = "Issue Topis/ Target Dates / Status"
Range("AY4:BB4").Select
With Selection.Font
.Name ="Arial"
.Size = 8
End With
'Add Border
Dim lastRow1 As Long
lastRow1 = Cells(Rows.Count,"AY").End(xlUp).Row
CallSetRangeBorder(Range("AX5:BB" & lastRow1))
'Change font
Range("AX5:BB"& lastRow1).Select
With Selection.Font
.Size = 8
End With
'Add Sequential Numbers
Range("AX5:AX"& lastRow1).formula = "=IF(BC5=BC4,AX4+1,1)"
Range("AX:AX").Copy
Range("AX:AX").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode =False
'Add Sub-Header
Dim last_row As Long, iRow AsLong
Dim N As Long, i As Long
last_row = Sheets("OTRCMOR File").Range("AY" & Rows.Count).End(xlUp).Row
N = 2
'compare the values incolumn BC and insert headings when values are different
For i = 2 To last_row
If Sheets("OTRCMOR File").Range("BC" & N) <> Sheets("OTRC MORFile").Range("BC" & N + 1) Then
Sheets("OTRCMOR File").Range("AH40:AM40").Copy
Sheets("OTRCMOR File").Range("AX" & N + 1).Insert Shift:=xlDown
N = N + 2
Else:
N = N + 1
End If
Next
Application.CutCopyMode =False
'Select Merged cells in column AX and add header based on EntityOwnership (column BC)
Dim R As Range, Adr As String
With Application
.FindFormat.Clear
.ReplaceFormat.Clear
.FindFormat.MergeCells =True
End With
Set R =Range("AX:AX").Find("", SearchFormat:=True)
If Not R Is Nothing Then
Adr = R.Address
Do
R.FormulaR1C1 ="=R[1]C[5]"
R.Value = R.Value
Set R =Range("AX:AX").Find("", R, SearchFormat:=True)
If R Is Nothing ThenExit Do
If R.Address = AdrThen Exit Do
R.FormulaR1C1 = "=R[1]C[5]"
R.Value = R.Value
If R.Value = 0 Then
R.UnMerge
End If
Loop
Else
MsgBox "no mergedcells in col AX"
Exit Sub
End If
With Application
.FindFormat.Clear
.ReplaceFormat.Clear
End With
'Find 0 value in column AX and clear cell with adjacent cells
Dim r1 As Long
Dim LastR As Long
LastR = Range("AX"& Rows.Count).End(xlUp).Row
For r1 = 1 To LastR
If Cells(r1,"AX") = 0 Then
Range(Cells(r1,"AX"), Cells(r1, "BB")).Clear
End If
Next r1
'Find and replace "Area" and "Non-O&T"
Columns("AX:AX").Select
Selection.ReplaceWhat:="Area", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False,SearchFormat:=False, _
ReplaceFormat:=False
Selection.ReplaceWhat:="Non-O&T", Replacement:="Shared", LookAt:=xlPart_
, SearchOrder:=xlByRows,MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Count Notation
Range("AZ2").formula = "=COUNT(AX:AX) &""Auditson the Watch List(""&COUNTIF(BC:BC,""O&T Area"")&""Owned,""&COUNTIF(BC:BC,""Non-O&T"")&""Shared)"""
Range("AZ2").Select
With Selection.Font
.Name ="Arial"
.Size = 11
End With
Range("AZ2").Copy
Range("AZ2").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode =False
'Message Box
MsgBox "All done!"
End SubThank you
