Subtotal - copy to other worksheet
 

Thanks, it works but when I copy the totals to other worksheet but it adds
each time when I copy it.

Is there a way to clear the sheet2 and go to Cell A10 before making copy
from other sheet and also is there a way using "Macro" instead of command
button?

Thanks again...

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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

Subtotal - copy to other worksheet
 

can't you just paste the value instead of the formula?

--


Gary


"Bill" wrote in message ...
Thanks, it works but when I copy the totals to other worksheet but it adds
each time when I copy it.

Is there a way to clear the sheet2 and go to Cell A10 before making copy
from other sheet and also is there a way using "Macro" instead of command
button?

Thanks again...

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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

Subtotal - copy to other worksheet
 

It is set up below as a macro. I never said you had to put it in a
commandbutton.

Sub CopyDate()
Dim cell as Range, rng as Range
Dim col as Long
col = 5
Worksheets("Sheet2").Rows(10).Resize(2000).ClearCo ntents
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("Sheet2")
set cell = .Cells(rows.count,col).End(xlup)
if cell.Row < 10 then
set cell = .Range("A10")
end if
set cell = .cells(cell.row+1,1)
End With
ar.EntireRow.copy
cell.PasteSpecial xlPasteValues
cell.PasteSpecial xlPasteFormats
Next
End Sub

--
Regards,
Tom Ogilvy


"Bill" wrote in message ...
Thanks, it works but when I copy the totals to other worksheet but it adds
each time when I copy it.

Is there a way to clear the sheet2 and go to Cell A10 before making copy
from other sheet and also is there a way using "Macro" instead of command
button?

Thanks again...

"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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