I am offically going to go crazy! LOL. I have some VB code that is in a seperate workbook that I run via a button placed on the active worksheet. Here is the relevant section of code that "sometimes" causes issues.
The workbook is located on sharepoint but it would be open any time this macro is ran. I personally never have any issues at all with this macro, but others do. For some people it will work once but then it throws debug errors saying Object can not be found. The file open is named the exact same as indicated in the code. The rest of the code finds the workbook, so any idea why maybe the problem section "sometimes" throws a unable to locate object error?
I probably have some duplicate code that isn't nessecary as I tend to go in and add little bits here and there. Portions of the code were helped to be put together with some forum members and I appredicate that. I probably have a few extra activate lines but for some reason even tho the With's would refer to a certain sheet it wouldnt actually do the tasks on that sheet unless I activated it.
Worksheets("BO Ref List").Activate
Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Ref List").Range("B:D")
r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
Worksheets("FO Ref List").Activate
Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Ref List").Range("B:D")
r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
Windows("P-score and Q-score trends.xlsx").Activate
With ActiveWorkbook.Names("BOAssociate")
.Name = "BOAssociate"
.RefersToR1C1 = _
"=OFFSET('BO Ref List'!RC[-2],0,0,COUNTA('BO Ref List'!R2C2:R989C2),1)"
.Comment = ""
End With
This is the complete code just in case there is something else causing the problem.
Sub RefreshLists()
'
' Macro to Refresh lists, will clear and fill new data.
'
'
' BO Section
Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Ref List").Activate
With Workbooks("P-score and Q-score trends.xlsx")
.Worksheets("BO Ref List").Cells.ClearContents
.Worksheets("BO Overall Data").Columns("G:H").Copy
With .Worksheets("BO Ref List")
.Range("A1").PasteSpecial
.Columns("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
.Range("A2:B2").Delete Shift:=xlUp
With .Range("B2", .Range("B2").End(xlDown)).Offset(, 1)
.FormulaR1C1 = "=INDEX('BO Overall Data'!C6, MATCH(RC1,'BO Overall Data'!C7,0) + COUNTIF('BO Overall Data'!C7, RC1) - 1)"
.Value = .Value
End With
.Range("C1").Value = "Most Recent Manager"
.Columns("C").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("D1"), _
Unique:=True
.Range("D1").Value = "Unique Manager List"
With .Range("C1:D1")
.Interior.Pattern = xlSolid
.Interior.Color = 65535
.Font.Bold = True
End With
Worksheets("BO Ref List").Range("D1:D200").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Worksheets("BO Ref List").Range("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End With
' FO Section
Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Ref List").Activate
With Workbooks("P-score and Q-score trends.xlsx")
.Worksheets("FO Ref List").Cells.ClearContents
.Worksheets("FO Overall Data").Columns("G:H").Copy
With .Worksheets("FO Ref List")
.Range("A1").PasteSpecial
.Columns("A:B").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
.Range("A2:B2").Delete Shift:=xlUp
With .Range("B2", .Range("B2").End(xlDown)).Offset(, 1)
.FormulaR1C1 = "=INDEX('FO Overall Data'!C6, MATCH(RC1,'FO Overall Data'!C7,0) + COUNTIF('FO Overall Data'!C7, RC1) - 1)"
.Value = .Value
End With
.Range("C1").Value = "Most Recent Manager"
.Columns("C").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("D1"), _
Unique:=True
.Range("D1").Value = "Unique Manager List"
With .Range("C1:D1")
.Interior.Pattern = xlSolid
.Interior.Color = 65535
.Font.Bold = True
End With
Worksheets("FO Ref List").Range("D1:D200").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
Worksheets("FO Ref List").Range("A:C").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End With
End With
End With
Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Occurance Trend").PivotTables("BO").PivotCache.Refresh
Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Occurance Trend").PivotTables("FO").PivotCache.Refresh
Dim r As Range
Worksheets("BO Ref List").Activate
Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("BO Ref List").Range("B:D")
r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
Worksheets("FO Ref List").Activate
Set r = Workbooks("P-score and Q-score trends.xlsx").Worksheets("FO Ref List").Range("B:D")
r.Value = Evaluate("IF(ISNUMBER(" & r.Address & ")," & r.Address & ",PROPER(" & r.Address & "))")
Windows("P-score and Q-score trends.xlsx").Activate
With ActiveWorkbook.Names("BOAssociate")
.Name = "BOAssociate"
.RefersToR1C1 = _
"=OFFSET('BO Ref List'!RC[-2],0,0,COUNTA('BO Ref List'!R2C2:R989C2),1)"
.Comment = ""
End With
Workbooks("Macros.xlsm").Close
Workbooks("P-score and Q-score trends.xlsx").Active
MsgBox ("Complete")
End Sub
Bookmarks