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.

What about running the ClearContents before copying the data? — Tim Williams, Jan 06 '22 at 23:31
Search this site for the exact error message you're getting (*run-time error 1004 application-defined or object-defined error*). This question has been asked and answered here many times before. — Ken White, Jan 07 '22 at 01:24

VBasic2008 · Accepted Answer · 2022-01-07T01:26:21.760

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

Thank you! I went through the post that you mentioned but I couldn't really understand it :( But your example made it clearer. Thank you so much for taking the time! — Gabriel Sotero, Jan 07 '22 at 11:41
This code doesn't use `Select`. When you run it whatever you have active or selected, stays active and selected. When you use `Select` or `Activate`, the selection changes, it 'jumps' from sheet to sheet, from range to range... thus slowing down your code. For this particular case, it is enough to study this code. For a wider understanding, study the accepted answer in the suggested link. — VBasic2008, Jan 07 '22 at 11:47

Pasting after ClearContents in VBA

1 Answers1

Copy Excel Table Column (ListColumn.Range)

Copy Excel Table Column (`ListColumn.Range`)