PDA

View Full Version : [SOLVED] how to copy the visible cells of a table to another tab?



elsg
02-21-2014, 07:34 AM
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-questions/759061-how-copy-visible-cells-table-another-tab.html

p45cal
02-21-2014, 09:04 AM
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
02-21-2014, 10:50 AM
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
02-21-2014, 02:38 PM
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
02-21-2014, 05:44 PM
Hi p45cal, i like it!

Very good!!

thank you very much!!!!!!!!!!!!!!:thumb