Excel - Copy Filtered Rows from a table to a new blank worksheet

[MattGreer]

I'm trying to copy a filtered table to another worksheet. I only want to copy the filtered rows, but I also want the formulas to reference the new table, not the old table.

From what I've seen, the only to copy formulas from one table to a new table correctly is to use [tt]PasteSpecial Paste:=xlPasteAll[/tt], but, if I use that, it copies ALL the rows, not just the filtered rows.

Here's my code, which copies all the rows. I've tried using other [tt]Paste:=[xlWhateverIsThere][/tt] but it doesn't work. The formulas reference the old table.

[tt]
Public Sub create_archive_table()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb As Workbook
Dim lo As ListObject
Dim s1 As String, s2 As String

s1 = "project_list"
s2 = "project_archive"

Set wb = ThisWorkbook
Set ws1 = wb.Sheets(s1)
Set lo = ws1.ListObjects("tblProjects")

Sheets.Add(After:=Sheets(s1)).Name = s2
Set ws2 = wb.Sheets(s2)

wb.SlicerCaches("Slicer_status").SlicerItems("Canceled").Selected = True
wb.SlicerCaches("Slicer_status").SlicerItems("Complete").Selected = True

lo.Range.SpecialCells(xlCellTypeVisible).Copy

With ws2.Range("A1")
.PasteSpecial Paste:=xlPasteAll
' .PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
' .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' .PasteSpecial Paste:=xlPasteValues
' .PasteSpecial Paste:=xlPasteFormulas
' .PasteSpecial Paste:=xlPasteFormats
.PasteSpecial Paste:=xlPasteColumnWidths
.Select
End With

Application.CutCopyMode = False

Set wb = Nothing
Set ws1 = Nothing
Set ws2 = Nothing
Set lo = Nothing

End Sub
[/tt]

Thanks!!


Matt

 

[MattGreer]

Oh man... is it not possible to do this then?

Thanks!!


Matt

 

[SkipVought]

Matt,

Copy the formula TEXT. This is not the same as copying the RANGE. You must EDIT the cell and copy the formula in the Formula Bar.

When you copy the formula TEXT you must be aware of the Cell being edited. You must edit the same cell in the target worksheet, being certain that any Named Range, PivotTable or Structured Table reference exists in the target workbook OR you must edit the formula accordingly after you paste it into the Formula Bar in the target sheet.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

Maybe we need to see the formula in question.

Should be no problem copying the filtered data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MattGreer]

Hiya Skip! Hope you're doing well.

I was disappointed that all the solutions I saw on the internet said essentially the same thing and none of them worked for me, so I posted the question here because of the outstanding expertise of the folks here. However, A few minutes after I posted my follow up message I gave up and just deleted the unnecessary rows off the pasted/destination table. So, copy the source table, PasteAll at the destination, filter the destination by the rows I don't need, and delete the filtered rows. Works well. But I'd still like to know if it can be done at the source, rather than the destination.

So skip, you say:

Copy the formula TEXT. This is not the same as copying the RANGE. You must EDIT the cell and copy the formula in the Formula Bar.

So if I understand you correctly, I would have to do this manually in the source table: click on the cell, click in the formula bar, select all, copy, then paste the formula in the destination table?

Thanks!!


Matt

 

[combo]

I'm trying to copy a filtered table to another worksheet.

Do you have filtered structured table to copy? If so, does the target has to be structured table too? With the same name? Are there regular or structured formulas to copy?

combo

 

[SkipVought]

Copying a filtered table...I'd rather QUERY that table to grab the data that you had filtered. That gets you VALUES.

The question: what's the formula and how to apply in the target workbook?

How often do you need to perform this process?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

If your table has only structured formulas and they refer only to tblProjects, you can, replacement from [tt]lo.Range.SpecialCells(xlCellTypeVisible).Copy[/tt]:

Dim t As String, s As String, c As Range

ws1.ListObjects("tblProjects").Range.Copy

ws2.Range("A1").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
ws2.ListObjects.Add(SourceType:=xlSrcRange, xllistobjecthasheaders:=xlYes, Destination:=Range("A1")).Name = "TestTable"
t = "tblProjects"

For Each c In ws2.ListObjects("TestTable").DataBodyRange.Rows(1).Cells
    ' process only first row, excel will copy down formulas
    ' hope that that are all possible replacements
    If Left(c.Formula, 1) = "=" Then
        s = c.Formula
        s = Replace(s, "=" & t & "[", "=[")
        s = Replace(s, "(" & t & "[", "([")
        s = Replace(s, "+" & t & "[", "+[")
        s = Replace(s, "-" & t & "[", "-[")
        s = Replace(s, "*" & t & "[", "*[")
        s = Replace(s, "/" & t & "[", "/[")
        s = Replace(s, "^" & t & "[", "^[")
    c.Formula = s
    End If
Next c

' to unlist TestTable table
' however formulas will reference ws2 explicitly
ws2.ListObjects("TestTable").Unlist

The code adds new table and removes external reference to tblProjects from structured formulas.

EDIT:
Actually, single Replace can remove references to external table:
[tt]s = Replace(s, t , "")[/tt]

combo