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


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=383640

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


--
Raman325
------------------------------------------------------------------------
Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748
View this thread: http://www.excelforum.com/showthread...hreadid=383640

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...
--
HTH...

Jim Thomlinson


"Craigm" wrote:


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


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=383640

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.
--------------------------------------
All of your input is valuable!

Thanks, Craigm


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=383640

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...


--
HTH...

Jim Thomlinson


"Craigm" wrote:


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.
--------------------------------------
All of your input is valuable!

Thanks, Craigm


--
Craigm
------------------------------------------------------------------------
Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
View this thread: http://www.excelforum.com/showthread...hreadid=383640