Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
MattGreer

MattGreer

Technical User
Joined
Feb 19, 2004
Messages
471
Location
US
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
 
Sort by date Sort by votes
Oh man... is it not possible to do this then?

Thanks!!


Matt
 
Upvote 0 Downvote
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,

[glasses]Just 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!
 
Upvote 0 Downvote
Maybe we need to see the formula in question.

Should be no problem copying the filtered data.

Skip,

[glasses]Just 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!
 
Upvote 0 Downvote
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:
SkipVought said:
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.
Click to expand...

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
 
Upvote 0 Downvote
MattGreer said:
I'm trying to copy a filtered table to another worksheet.
Click to expand...
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
 
Upvote 0 Downvote
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,

[glasses]Just 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!
 
Upvote 0 Downvote
If your table has only structured formulas and they refer only to tblProjects, you can, replacement from [tt]lo.Range.SpecialCells(xlCellTypeVisible).Copy[/tt]:
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:
[tt]s = Replace(s, t , "")[/tt]

combo
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
Andrzejek
  • Locked
  • Question Question
Copy data between SQL and Oracle
Replies
8
Views
2K
remeng
remeng
remeng
  • Locked
  • Question Question
Delete dynamic table rows without removing password protection
Replies
7
Views
873
remeng
remeng
R
  • Locked
  • Question Question
reversed words in english leading new english words
Replies
10
Views
1K
Chriss Miller
C
MattGreer
  • Locked
  • Question Question
Delete a Table Row in a Filtered Table
Replies
11
Views
2K
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top