What the subject says. Just wondering if anyone has had this experience before?
- Workbook has numerous macros and steps to automate a process.
- When I run through all steps the first time, everything works perfectly.
- I close the file and save.
- When I open the file and re-run the process with new days' data, I get an error message on step 10
- other steps had data i cleared and then pasted new day data into, and all other macros work fine.
- step 10 error happens on PasteSpecial command (see below)
- i'm just copying some column data from one filtered table into another table that i had cleared.
- I can choose "End" or "Debug" on the error message. I choose "End"
- Without changing anything, I simply kick of step 10 again and it works perfectly now.
It's an easy thing to do, but I don't want my users to have to do this obviously. I recently upgraded to Win 365.
Has anyone experienced something like this and know what causes it?
Sub SherlocSummary()
Application.ScreenUpdating = False
Dim sht As Worksheet
Set sht = ActiveSheet
If Range("AZ3").Value = "" Then
MsgBox "Please ensure there is data in Sherloc download (Steps 5-8) first.", vbCritical, "No data"
GoTo EndIt:
End If
If Range("CZ3").Value <> "" Then
Call ClearThirdPull
Application.ScreenUpdating = False
End If
Application.EnableEvents = False
BotRowSortRng = Range("AZ50000").End(xlUp).Row
Dim Rng, sortcol1, sortcol2 As Range
Set Rng = Range("AZ2:CW" & BotRowSortRng)
If sht.ListObjects("tblSherlocFinal").ShowAutoFilter = True Then sht.ListObjects("tblSherlocFinal").AutoFilter.ShowAllData
If sht.ListObjects("tblSherlocRaw").ShowAutoFilter = True Then sht.ListObjects("tblSherlocRaw").AutoFilter.ShowAllData
Set sortcol1 = Range("tblSherlocRaw[Last Event Cd]")
With sht.ListObjects("tblSherlocRaw").Sort
.SortFields.Clear
.SortFields.Add Key:=sortcol1, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
sht.ListObjects("tblSherlocRaw").Range.AutoFilter Field:=45, Criteria1:="<>OK"
TryAgain:
Columns("AZ:CV").Hidden = False
If sht.ListObjects("tblSherlocRaw").DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count = 0 Then GoTo NoRows
On Error GoTo TryAgain
Call SherlocPasteSub("Clock Start", "StartClock Date")
Call SherlocPasteSub("HWB No", "Waybill Number")
Call SherlocPasteSub("Piece No", "Pieces")
Call SherlocPasteSub("Orig Ctry", "Origin Country/Territory Code")
Call SherlocPasteSub("Orig Stn", "Origin Service Area Code")
Call SherlocPasteSub("Dest Ctry", "Destination Country/Territory Code")
Call SherlocPasteSub("Dest Stn", "Destination Service Area Code")
Call SherlocPasteSub("Last Event Stn", "Last Event Stn")
Call SherlocPasteSub("Last Event Cd", "Last Event")
Call SherlocPasteSub("Last Event Dtm", "Last Event Timestamp")
Call SherlocPasteSub("Last Event Class", "Last Event Class")
Call SherlocPasteSub("EDD", "EDD")
Columns("AZ:CV").Hidden = True
NoRows:
If sht.ListObjects("tblSherlocRaw").ShowAutoFilter = True Then sht.ListObjects("tblSherlocRaw").AutoFilter.ShowAllData
Range("CY:CY,DH:DH,DJ:DJ").NumberFormat = "m/d/yyyy"
Set sortcol2 = Range("tblSherlocFinal[StartClock Date]")
With sht.ListObjects("tblSherlocFinal").Sort
.SortFields.Clear
.SortFields.Add Key:=sortcol2, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With
EndIt:
Range("CY3").Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sub SherlocPasteSub(RawCol, FinalCol)
Dim lc As ListColumn
Dim col As Long
Dim sht As Worksheet
Set sht = ActiveSheet
With sht
.ListObjects("tblSherlocRaw").ListColumns(RawCol).DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
Set lc = .ListObjects("tblSherlocFinal").ListColumns(FinalCol)
col = lc.Range.Column
.Cells(3, col).Select
.PasteSpecial <--- the error happens here
End With
End Sub
