Select range in table.

[rpaulson]

see code below,
it works as needed.
just like to add a small tweak.

my table has 6 fields,
current code below will copy all 6 (Coumns A~F)
what is the most efficient way to exclude first 2 columns, as i only need columns (C~F)

Dim lo As ListObject
Set lo = Worksheets("InvoiceDetail").ListObjects("Invoice_Detail")
lo.Range.AutoFilter Field:=1, Criteria1:=[D2] 'filter field1 by value in cell D2

lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy

[S11].PasteSpecial Paste:=xlPasteValues
......

thanks for looking,
Ross

 

[Jeffrey Mahoney]

Please try this code:

Dim lo As ListObject
  Dim Rng As Range
  Set lo = Worksheets("InvoiceDetail").ListObjects("Invoice_Detail")
  lo.Range.AutoFilter Field:=1, Criteria1:=[D2] 'filter field1 by value in cell D2
  
  Set Rng = Range(lo.ListColumns(3).DataBodyRange, lo.ListColumns(6).DataBodyRange)
  Rng.SpecialCells(xlCellTypeVisible).Copy
  
  [S11].PasteSpecial Paste:=xlPasteValues

 

[rpaulson]

Perfect thanks!