I everyone,

I have a very simple Excel function trying to copy data from a table into another sheet. I have the following code:

When I comment out the ClearContents command, it works, but if this is making a

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

I have already looked through stack overflow and internet but no luck

Thanks in advance

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

CodePudding user response：

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