ActiveSheet.ListObjects copy only specific columns from a table

[Mike756]

Hi!

My problem seems rather easy but I cannot get it to work. I need to copy a table, but I do not want all columns.
The below code works but not as expected. It seems to select the right columns (2,3 and 5), but when I paste the result, I get columns 2,3,4, and 5.

Why is that? I do not want column 4. How can I do that?

With ActiveSheet.ListObjects("Table1")
   Union(.ListColumns(2).Range, _
              .ListColumns(3).Range, _
              .ListColumns(5).Range).Copy
End With

Thanks for the help.
Mike

 

[Mike756]

I just realized that it actually works when I paste it into an Excel sheet. When I paste it into an HTML email (Outlook), it also pastes all columns between the selection. I really don't understand why this is happening...

 

[Mario_R]

Hello. Try with:

Dim a, i&, j%
With ActiveSheet.ListObjects(1).Range
  ReDim a(1 To .Rows.Count, 1 To 1)
  For i = 1 To .Rows.Count
    a(i, 1) = .Cells(i, 2).Text & vbTab & .Cells(i, 3).Text & vbTab & .Cells(i, 5).Text
  Next
End With

 

[Mike756]

Hello. Try with:

Dim a, i&, j%
With ActiveSheet.ListObjects(1).Range
  ReDim a(1 To .Rows.Count, 1 To 1)
  For i = 1 To .Rows.Count
    a(i, 1) = .Cells(i, 2).Text & vbTab & .Cells(i, 3).Text & vbTab & .Cells(i, 5).Text
  Next
End With

Hi! Thanks...sorry for my ignorance but how do I copy the result of this code? I need to get it into my clipboard.