tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi there,
I have the following VBA code which works fine on my system but when someone else opens it on their computer, it's returning "Runtime Error 438".
Since it's not showing me the error, it's difficult to narrow in on. Is there anything obvious that may be causing it?
Thank you!
I have the following VBA code which works fine on my system but when someone else opens it on their computer, it's returning "Runtime Error 438".
Since it's not showing me the error, it's difficult to narrow in on. Is there anything obvious that may be causing it?
Thank you!
VBA Code:
Sub Prepare()
'
' Prepare Macro
'Unprotect sheet
Sheets("Stocktake Print Sheets").Select
ActiveSheet.Unprotect
'Select table "Member_Table_Subtotal"
Application.Goto Reference:="Member_Table_Subtotal"
'Subtotal = Remove all
Selection.RemoveSubtotal
'Remove filters
Selection.AutoFilter
'Select table "Member_Table"
Application.Goto Reference:="Member_Table"
'Sort table by "Team Member" then by "Location"
ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
:=Range("D6:D122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort.SortFields.Add2 Key _
:=Range("C6:C122"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Stocktake Print Sheets").Sort
.SetRange Range("A5:G122")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Add filter, Under title "Location" hide "Blank"
Selection.AutoFilter
Range("$A$5:$G$122").AutoFilter Field:=3, Criteria1:="<>Blank"
'Add subtotals
Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(1), _
Replace:=False, PageBreaks:=True, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=-24
'Go to top of table
Range("A5").Select
'Protect Sheet
Sheets("Stocktake Print Sheets").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub