ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copying filtered rows (https://www.excelbanter.com/excel-programming/388972-copying-filtered-rows.html)

Oldjay
Copying filtered rows
 
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

JMay
Copying filtered rows
 
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

"Oldjay" wrote:

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

Oldjay
Copying filtered rows
 
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

"JMay" wrote:

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

"Oldjay" wrote:

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

Oldjay
Copying filtered rows
 
Thanks for the help

"Oldjay" wrote:

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

"JMay" wrote:

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

"Oldjay" wrote:

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

JMay
Copying filtered rows
 
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.

"Oldjay" wrote:

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

"JMay" wrote:

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

"Oldjay" wrote:

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


All times are GMT +1. The time now is 09:59 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com