×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread707-1819098

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

Thanks!!


Matt

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

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

Thanks!!


Matt

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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

Maybe we need to see the formula in question.

Should be no problem copying the filtered data.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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

(OP)
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:

Quote (SkipVought)

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

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

Quote (MattGreer)

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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue
"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

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

CODE --> 

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:
s = Replace(s, t , "")

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close