Visible rows copy to different worksheet

[Craigm]

ActiveSheet.Outline.ShowLevels RowLevels:=2

In a macro I set a data filter to obtain Subtotals (like on the data
menu). Now instead of 8,000 rows I have 50 Subtotal rows that are
visible. The numbers of rows varies by data set.

When I copy the data (to a different worksheet) all 8,000 rows are
copied.

How do I copy only the visible rows?

Cornfused but ultimately greatful for your help.

Craigm

 

[Raman325]

Not sure if this will do it and this might be a slow way to handle it
but u could take it line by line, check to see if its visible, and if
so copy and paste into the next line on the second sheet. You would
have to have two counters, one for the individual rows in the original
sheet and one for the rows in the second sheet

 

[Jim Thomlinson]

Select the cells that you want to copy -> Hit F5 -> Special (Bottom Left
Corner) -> Select Visible Cells Only -> Ok -> Right Click -> Copy ... and you
can paste this wherever you want... This can also be done in code if you need
that...

 

[Craigm]

I am forcing the copy with a For loop to run all the way down the
worksheet (all 8,000 rows) and copy the data to a new worksheet using
InStr to find my "Totals".

There must be a way to only copy the visible cells to another
worksheet?

I cannot even chart with the autofilter on as the data exceeds the
number of rows as input to the chart.

I am a lost puppy at this point.

 

[Jim Thomlinson]

Sorry about taking so long to get back to you... Is there a reason that you
are looping instead of using find and findnext? Other than that a range has a
property called specialcell which takes an argument (in this case
xlVisibleCellsOnly) so something like this.
Range("A1:A8000").specialcells(xlVisibleCellsOnly).copy

If you want help with the find - find next which would be a pile more
efficient just ask...

 

[Craigm]

I'm askin...I'm askin!: Help with Find and Find Next?

I'm using the Subtotals funtionality in macros. I get 50 Subtotal rows
(instead of 8,000 original rows). I need to copy the 50 visible rows to
a new worksheet. I am doing this brute force with a For loop.

If there is a better way "Find Next" or copy "xlVisibleCellsOnly" I
would appreciate your help in understanding the methods.

Thanks! Craigm