Subtotal - copy to other worksheet

[Bill]

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

 

[Gary Keramidas]

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

 

[Tom Ogilvy]

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