Solved: Copy subtotal values

[JohnyG]

Greetings,

I have an Excel workbook containing two sheets, (attached). I was trying to pick subtotals values and paste it in another sheet. I was able to do the subtotal however was unable to transfer the subtotal values to another sheet. Sheet1 contains the macro code and Sheet2 contains the output that i need.

Thank you in advance.


Johny:doh:

[JimmyTheHand]

Try this

Sub CopySubTotals()
Dim Src As Worksheet, Tgt As Worksheet, R1 As Range, R2 As Range

Set Src = Sheets("Sheet1")
Set Tgt = Sheets("Sheet2")
Src.Outline.ShowLevels 3
Set R1 = Src.Range("A1").End(xlDown)
Do
Set R2 = Tgt.Range("A" & Tgt.Rows.Count).End(xlUp).Offset(1)
R2 = R1
R2.Offset(, 1) = R1.Offset(1, 1)
R2.Offset(, 2) = R1.Offset(1, 2)
Set R1 = R1.End(xlDown).End(xlDown)
Loop While R1.Row < Src.Rows.Count
End Sub

[JohnyG]

Thanks Jimmy that did the trick ...

One last questiion ... I am trying to put Vlookup in the same sheet. I have figured out the formula for that and in excel it is working absolutely fine. However, i am unable to implement it thru VBA.

I need to put this formula in Cell D2 (Test1.xls! sheet2).

=IF(ISNA(VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$1000,3,FALSE)),"", VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$1000,3,FALSE))

Note- [Theirs.xls!Payables] is the name of the sheet from where i need to compare values.

Please guide me.

[JimmyTheHand]

Formulas are tricky.

I guess
Range("D2").Formula = " =IF(ISNA(VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$1000,3,FALSE)), " _
& """"", VLOOKUP(A2,[Theirs.xls]PAYABLES!$A$2:$C$1000,3,FALSE))"
should do.
But if it doesn't, then I suggest that you record a macro while typing in the correct formula, then modify the recorded code.

Jimmy

[JohnyG]

Thanks for all your guidance and valuable support.


Best Regards,