ExcelBanter - Copy and paste subtotal to another worksheet

Hi

You can use find

With the Total in col A of sheet1 and in Col B the numbers
Try this macro to copy to Sheet2 A1

Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim MyArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim I As Long

Application.ScreenUpdating = False
'You can also use more values in the Array
MyArr = Array("Total")

Rcount = 0
With Sheets("Sheet1").Range("A1:A100")

For I = LBound(MyArr) To UBound(MyArr)
'If you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "@"
'Note : I use xlPart in this example and not xlWhole

Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rcount = Rcount + 1
' This example will only copy the value
Sheets("Sheet2").Range("A" & Rcount).Resize(1, 2).Value _
= Rng.Resize(1, 2).Value
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address < FirstAddress
End If
Next I
End With
Application.ScreenUpdating = True
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl

"JC" wrote in message om...
I used the macro recording to sort the list first and subtotal them.
It works fine but I also want to transfer(copy) the subtotal of the
worksheet to another worksheet in a specified cell location. I tried a
couple times and it did not work. I just want to know is it doable?
and how? I am very good with excel. I know how to use the macro
recording and I am learning how to write Macro from text book and CD.
I am new to this board and I appreciate your help.