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

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

Thanks, Craigm

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


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

[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