1

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.

Community
  • 1
  • 1
Gabriel Sotero
  • 155
  • 1
  • 13

1 Answers1

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
VBasic2008
  • 28,653
  • 5
  • 16
  • 24
  • 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