View Full Version : Solved: Copy subtotal values
JohnyG
06-04-2008, 10:34 PM
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
06-05-2008, 02:13 AM
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
06-05-2008, 03:00 AM
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
06-05-2008, 03:22 AM
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
06-05-2008, 04:10 AM
Thanks for all your guidance and valuable support.
Best Regards,
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.