Copying filtered rows

O

Oldjay

After I filter list I want to copy only the visible rows but with the below
code it copies the hidden rows.
also
Private Sub CommandButton1_Click() ' Analyze records

Dim c As Range
Dim ws As Worksheet
Dim iEnd As Long
Dim ws1 As Worksheet

UndoMyFilter

Set ws = Sheets("Summary")

iEnd = ws.Range("B7").End(xlDown).Row

For Each c In ws.Range("B7:B" & iEnd)
If c < ws.Range("G1") Then c.EntireRow.Hidden = True
Next c
For Each c In ws.Range("B26:B" & iEnd)
If c > ws.Range("G2") Then c.EntireRow.Hidden = True
Next c
For Each c In ws.Range("B7:B" & iEnd)
If c.Offset(0, 2) <> ws.Range("G3") Then c.EntireRow.Hidden = True
Next c

Application.CutCopyMode = False
Range("B6:F6").Select

Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Analyze").Select
Range("E36").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


End Sub
 
J

JMay

Here's a macro I use (often).


Sub DumpAutoFilterToNewSheet()
Dim ws As Worksheet ' Your Current sheet - the sheet with the Auto-Filter
Dim ws1 As Worksheet ' This will be a newly added sheet
Set ws = ActiveSheet
Set ws1 = Worksheets.Add
ws.Activate
ws.AutoFilter.Range.Copy _
Destination:=ws1.Cells(1, 1) ' Pastes into A1 - Change to suit
End Sub
 
O

Oldjay

When I copy
ws.Activate

ws.AutoFilter.Range.Copy _
Destination:=ws1.Cells(36, 5)
I get an error
Note
I am not auto filtering . I am just hiding rows and then trying to copy the
visible rows to another sheet
 
O

Oldjay

Thanks for the help

Oldjay said:
When I copy
ws.Activate

ws.AutoFilter.Range.Copy _
Destination:=ws1.Cells(36, 5)
I get an error
Note
I am not auto filtering . I am just hiding rows and then trying to copy the
visible rows to another sheet
 
J

JMay

Well using auto-filter would certainly be easier (on you), even if you just
created a new column (at the far right - last column) entitled Select? and
include this in the auto-filter table list - Just enter a "Y" (w/o the " "'s)
in the rows you wish to be visible and copy; then presto, and use my
suggested macro.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top