how to copy the visible cells of a table to another tab?

[elsg]

I need to copy the visible cells of a table to another tab?

Sub CopyTableFiltered()    Dim DtFirst As Long, DtLast As Long    DtInic = Range("J1").Value
    DtFinal = Range("K1").Value
    ActiveWorkbook.Sheets("Orig").ListObjects("Tabela1").Range.AutoFilter field:=1, Criteria1:=">=" & DtFirst, _
    Operator:=xlAnd, _
    Criteria2:="<=" & DtLast
    'I need copy visible data in table to another worksheet ("Dest"). End Sub

Cross-Post
http://www.mrexcel.com/forum/excel-q...other-tab.html

[p45cal]

ActiveWorkbook.Sheets("Orig").ListObjects("Tabela1").Range.SpecialCells(xlVisible).Copy Sheets("Sheet9").Range("A1")

Though you should ensure there's no confusion in variable names: DtFirst v. DtInic and DtLast v. DtFinal.

[elsg]

first thank you very much!

I would like to ask one more question, how to avoid copying the last row of the table?


look at my file and understood

[p45cal]

Sub CopyTableFiltered()
Dim DtFirst As Long, DtLast As Long
DtFirst = Sheets("Orig").Range("J1").Value
DtLast = Sheets("Orig").Range("K1").Value
With ActiveWorkbook.Worksheets("Orig").ListObjects("Tabela1")
  .Range.AutoFilter field:=1, Criteria1:=">=" & DtFirst, Operator:=xlAnd, Criteria2:="<=" & DtLast
  Union(.HeaderRowRange, .DataBodyRange.SpecialCells(xlVisible)).Copy Sheets("Dest").Range("A1")
End With
End Sub

edit post posting:
a more robust version:

Sub CopyTableFiltered()
Dim DtFirst As Long, DtLast As Long, RngToCopy As Range
DtFirst = Sheets("Orig").Range("J1").Value
DtLast = Sheets("Orig").Range("K1").Value
With ActiveWorkbook.Worksheets("Orig").ListObjects("Tabela1")
  .Range.AutoFilter field:=1, Criteria1:=">=" & DtFirst, Operator:=xlAnd, Criteria2:="<=" & DtLast
  On Error Resume Next
  Set RngToCopy = Union(.HeaderRowRange, .DataBodyRange.SpecialCells(xlVisible))
  On Error GoTo 0
  If Not RngToCopy Is Nothing Then
    RngToCopy.Copy Sheets("Dest").Range("A1")
  Else
    MsgBox "No records found"
  End If
End With
End Sub

[elsg]

Hi p45cal, i like it!

Very good!!

thank you very much!!!!!!!!!!!!!!