Occasionally get a Run-time Error 1004 on Selection.Pastespecial

[JaredHOASol]

Hello,
I am new to VBA and macro building. Additionally, I'm self taught, so I apologize if it appears unefficient.
The problem I'm having is that my macro occasionally fails when it does it's Selection.PasteSpecial.
When it happens, I select debug>Reset>Run Sub, and then it works for several more cycles before failing once again.
It happens to both of the Pastespecials (Again it happens sporadically for both) I have in the macro and I can't seem to determine why. Everything else appears to work.

Please assist me in find out why they occasionally fail. I can't find it.

Here's the code (I wrote the comments while using Notepad++ so if you paste this in that, the line comments so align up properly.):

Public Sub Workbook_Activate()
    'Built 2021/08/04 by Jared Hanson. Some of the code was found online and modified.
    'I'm self-taught in VBA and new to it, but I understand for the most part what is happening. After lots of research of course ;)
   
    'The Macro below is activated when the workbook is initially opened.
    'The other workbook "Report_Data", is also opened when this workbook is opened and then runs its own macro to pull data constantly from a PLC.
    'The code below tells the excel file "WW_Report" to run this code every 15 mins on the nose.
    'Additionally, if it is 23:55, Run the "Report_Print_Reset" Macro which saves a copy of the current values, Prints a hardcopy, then cleans out the template for new data.
    'This whole process is to operate autonomously.
   
    Dim CTime as Date
    Dim When as Date
       
        When = Now()
        CTime = Now()
        When = TimeValue(Hour(CTime) & ":" & Minute(CTime) + (1 - (Minute(CTime) Mod 1)) & ":00")
        Workbooks("Report_Data.xlsm").Activate
        Sheets("Group01").Select
        Application.OnTime When, "Complete"
        Application.OnTime "17:03:00", "Report_Print_Reset"
        'Application.Visible = True
 
End Sub





Public Sub Complete()
    'The code below Pulls data from another workbook, "Report_Data" and pastes it, transposed, onto another workbook, "WW_REPORT."

    Dim NextRow As Integer
    Dim NextColumn As Integer
    NextRow = 5
    NextColumn = 0
       
        'Application.Visible is here for troubleshooting reasons. The workbooks will be hidden to operate in the background on final release.
        Application.Visible = True
        Workbooks("Report_Data.xlsm").Activate
        Sheets("Group01").Select
        ActiveWorkbook.RefreshAll
       
        'The line below is grabbing a =Now() timestamp in another file that is running.
        Cells.Range("I10").Select
        Cells.Range("I10").Copy
        Workbooks("WW_REPORT.xlsm").Activate
        Sheets("INCOMING").Select
        NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
        Cells(NextRow, 1).Select
       
        'The Selection.PasteSpecial below occasionally fails. Run-time Error 1004, see below. Resetting the code then Re-running the sub from that spot continues the code with no issue until another failure.
        ' Run-Time Error 1004: PasteSpecial method of Range class Failed.
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        Selection.NumberFormat = "m/d/yy h:mm;@"
       
        'Below is going back to "Report_Data," then group an range of cells with data that updating frequently.
        Workbooks("Report_Data.xlsm").Activate
        Sheets("Group01").Select
       
        'If more cells are required, the below will need modified to catch all the values.
        Cells.Range("C10:C16").Select
        Cells.Range("C10:C16").Copy
        Workbooks("WW_REPORT.xlsm").Activate
        Sheets("INCOMING").Select
        Cells(NextRow, 2).Select
       
        'Does the same issue as the above Selection.PasteSpecial on line 53. Same error, same temporary resolution.
        'The content that it is copying is values in the form of text. This content, however, transposes its values.
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
       
        'Below is formatting. Adds a box to the new row of data.
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
            With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
            End With
            With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
            End With
            With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
            End With
            With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
            End With
      
       'Returns to the other workbook. It does this as that workbook has it's own 3rd party macro and will fail if it can't run its code. Idk, I didn't make it but I have to use it.
       'My lack of VBA knowledge requires me to leave it alone and make sure I have it selected.
       Workbooks("Report_Data.xlsm").Sheets("Group01").Activate
      
       Call Workbook_Activate
       
End Sub


Public Sub Report_Print_Reset()
       
        'The code below cleans up the "WW_Report" workbook, saves a copy of the report to the tower, then cleans the template and preps for the next day's values.
       
        Workbooks("WW_REPORT.xlsm").Activate
        Sheets("INCOMING").Select
        Columns("A:K").EntireColumn.AutoFit
        ActiveWorkbook.SaveCopyAs "C:\DAILYREPORTS\REPORTS\" & Format(Date, "yyMMmDD") & " " & Format(Time, "hh.mm") & " Turbidity_Report" & ".xls"
        Sheets("INCOMING").PrintOut
        Range("A5:Z500").Delete
        Workbooks("Report_Data.xlsm").Sheets("Group01").Activate

        Call Workbook_Activate

End Sub

 

[DrSimon]

Most of us are self taught for VBA and encounter errors like this as part of the learning curve. I don't know the answer to your problem but can suggest some ways to investigate.
Rather than debug>Reset>Run Sub when you encounter the issue, try using some of the tools available.
If you know which cells it fails on use Breakpoints to stop before it happens and use the Locals Window to check the values of objects and variables. I sometimes use an If statement and breakpoint to stop just before the crash occurs.
There is also debug.print which writes to the Immediate window so you can check specific values. You use the Immediate window to give values so ?Cells(NextRow, 2) will return that value.

In this case I suggest you look at what is being copied and also the cell that you are copying into to check that that what is being copied is valid.