Excel - Copy Filtered Rows from a table to a new blank worksheet
Excel - Copy Filtered Rows from a table to a new blank worksheet
(OP)
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 PasteSpecial Paste:=xlPasteAll, 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 Paste:=[xlWhateverIsThere] but it doesn't work. The formulas reference the old table.
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
From what I've seen, the only to copy formulas from one table to a new table correctly is to use PasteSpecial Paste:=xlPasteAll, 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 Paste:=[xlWhateverIsThere] but it doesn't work. The formulas reference the old table.
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
Thanks!!
Matt
RE: Excel - Copy Filtered Rows from a table to a new blank worksheet
Thanks!!
Matt
RE: Excel - Copy Filtered Rows from a table to a new blank worksheet
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,
Just traded in my OLD subtlety...
for a NUance!
"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!
RE: Excel - Copy Filtered Rows from a table to a new blank worksheet
Should be no problem copying the filtered data.
Skip,
Just traded in my OLD subtlety...
for a NUance!
"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!
RE: Excel - Copy Filtered Rows from a table to a new blank worksheet
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:
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
RE: Excel - Copy Filtered Rows from a table to a new blank worksheet
combo
RE: Excel - Copy Filtered Rows from a table to a new blank worksheet
The question: what's the formula and how to apply in the target workbook?
How often do you need to perform this process?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"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!
RE: Excel - Copy Filtered Rows from a table to a new blank worksheet
CODE -->
The code adds new table and removes external reference to tblProjects from structured formulas.
EDIT:
Actually, single Replace can remove references to external table:
s = Replace(s, t , "")
combo