At this moment i dont have a sample file that i can provide, if i can it would be next week and right now just looking for a quick solution until this is replaced.

So something i noticed and not sure what is the best solution at this moment.
When i run my macro, when it gets to this routine, i get this error:

error 1004 Select method of range class failed
On the 3rd line in the code below:
"ThisWorkbook.Worksheets("Cleanup").Range("A2").Select"

NOW, since it throws this error, if i simply select or make the "Cleanup" tab/sheet active, it will continue and complete successfully.

Im working on redoing this routine, but that wont be for a few more days maybe next week. And i have the rest of this week i have to run this report.
I havent had any issues with this until today, but today i swapped out another copy routine to use the advanced filter method to help speed things up.
Ill be trying to implement the same in this routine, but like i said not yet..

The only thing i can think is to add a "ThisWorkbook.Worksheets("Cleanup").Activate" before that line, but not sure thats the best solution. And then what will happen down towards the bottom when i need to paste the values into the other sheet "Mainrecords"

Private Sub SelectCopyCleanData()
    Dim LastRow As Long
    
    ' THIS GET A TOTAL COUNT OF ROWS BASED ON CLAIM NUMBER
    LastRow = ThisWorkbook.Worksheets("MainRecords").Range("A" & Rows.Count).End(xlUp).Offset(1).Row

   ThisWorkbook.Worksheets("Cleanup").Range("A2").Select

    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
  
    'PasteSpecial to paste values, formulas, formats, etc.
    ThisWorkbook.Worksheets("MainRecords").Range("A" & LastRow).PasteSpecial Paste:=xlPasteValues
    
    ThisWorkbook.Worksheets("Cleanup").Rows("2:" & Rows.Count).ClearContents
    
End Sub