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
|
Similar Threads
