Pasting after ClearContents in VBA

Question

I am trying to copy data from a table into another sheet.

Sub ListOfSquads()
  
    Sheets("Apontamentos").Select
    Range("Apontamentos[[#Headers],[Área]]").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    ThisWorkbook.Sheets("Squads").Select
        
    ThisWorkbook.Sheets("Squads").Columns("A:A").ClearContents
    
    ThisWorkbook.Sheets("Squads").Range("A1").Select
    
    ThisWorkbook.Sheets("Squads").Paste
    
    Application.CutCopyMode = False

End Sub

The ClearContents command is making a

Run-Time error '1004'> Application-defined or object-defined error.

Answer 1

Copy Excel Table Column (ListColumn.Range)

• In your code, when you did the ClearContents, you actually removed the copied range from the clipboard. As Tim Williams suggested in the comments, you had to move the ClearContents line before the Selection.Copy line and appropriately rearrange the Select lines.
• Using Select is a Macro Recorder 'thing' and is best avoided in your code, as described
  in this legendary post.
• This code uses the Range.Copy method.

Option Explicit

Sub ListOfSquads()
    
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Apontamentos")
    Dim stbl As ListObject: Set stbl = sws.ListObjects("Apontamentos")
    Dim slcl As ListColumn: Set slcl = stbl.ListColumns("Área")
    Dim scrg As Range: Set scrg = slcl.Range
    
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets("Squads")
    dws.Columns("A").ClearContents
    Dim dfCell As Range: Set dfCell = dws.Range("A1")
    
    ' Copy
    scrg.Copy dfCell
 
End Sub