Subtotal - copy to other worksheet

Hi,

I would be much appreciated if you can help me how to set the code. I know
how to use Subtotal and filter 2 for subtotal and copy to other worksheet by
manually. Is there a way to set macro automatic to hit to copy to other
worksheet with subtotal and grand-total only without detail records.

YOur help much appreciated.
Thanks

i've used something like this

Set rng = Range("i4:j" & lastrow)

For Each cell In rng
If UCase(Left(cell.Formula, 11)) = "=SUBTOTAL(9" then
' then put your copy code here
next
end if



--


Gary


"Bill" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> I would be much appreciated if you can help me how to set the code. I
> know how to use Subtotal and filter 2 for subtotal and copy to other
> worksheet by manually. Is there a way to set macro automatic to hit to
> copy to other worksheet with subtotal and grand-total only without detail
> records.
>
> YOur help much appreciated.
> Thanks
>

Assume you have your subtotal formulas in Column 5 and your detail data does
not contain formulas in column 5

Dim cell as Range, rng as Range
Dim col as Long
col = 5
on error resume next
set rng = Columns(col).SpecialCells(xlFormulas,xlNumbers)
On error goto 0
if rng is nothing then
msgbox "Nothing to copy"
exit sub
End if
for each ar in rng.Areas
With Worksheets("Summary")
set cell = .Cells(rows.count,col).End(xlup)
set cell = .cells(cell.row+1,1)
End With
ar.EntireRow.copy
cell.PasteSpecial xlPasteValues
cell.PasteSpecial xlPasteFormats
Next


--
Regards,
Tom Ogilvy



"Bill" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> I would be much appreciated if you can help me how to set the code. I
know
> how to use Subtotal and filter 2 for subtotal and copy to other worksheet
by
> manually. Is there a way to set macro automatic to hit to copy to other
> worksheet with subtotal and grand-total only without detail records.
>
> YOur help much appreciated.
> Thanks
>
>