need help in financial problem with vba?

[hoadiem]

i would like to create a vba code to resolve this problem.

data:

column A is acc# and column B is amount ($)

i want to make a vba code that will search to see if the 2 sheet has same acc# and then it will add them up, but if it cannot find one acc# in sheet 2, it still need to show the amount on that acc#

note: sheet 1 and sheet 2 may consist of different acc# but the main point is merge the amount for each acc# in a new sheet(sheet 3) and sheet 3 has a list of acc# in rows. just to post the amount next to acc# in sheet 3

and 1 more thing, sheet 1 or sheet 2 may contain 1 acc# but on 2 or more rows.

any help is appreciated. thanks a lot

 

[pedie]

I'm sure there will be many ways to do this....however instead of looping and finding and then suming etc...
i've just combined your data to sheet3 from sheet1 and sheet2 and done a pivot..

make required changes... if it error....
this is simple recorded and edited code;

[/FONT]
[FONT=Courier New]Sub GroupandSumdata_TEST()
    Dim mypvtrng As Range
    Sheets("Sheet1").Range("A4").CurrentRegion.Copy
    Sheets("Sheet3").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheets("Sheet2").Range("A4").CurrentRegion.Copy
    Sheets("Sheet3").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Set mypvtrng = Range("A1").CurrentRegion
    Application.CutCopyMode = False
    
    On Error Resume Next
     Sheets("Pivot").Delete
    On Error GoTo 0
    
    Sheets.Add
    ActiveSheet.Name = "Pivot"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mypvtrng, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:=Sheets("Pivot").Range("A1"), TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("Pivot").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Acc#")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("Amount$"), "Count of Amount$", xlCount
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Amount$")
        .Caption = "Sum of Amount$"
        .Function = xlSum
    End With
End Sub[/FONT]
[FONT=Courier New]
[/FONT] 
[FONT=Courier New]

 

[hoadiem]

i think it's better to use the search rather using copy and paste, because this project has a lot of sheets(over 20), i'm just giving 2 sheets for sample. and the location of the acc# is not the same on every sheet. and as i told , sheet 3 has data and info on it, and the total amount should be placed next to where the Acc# located. so copy and paste is not working in this case
and i would not want to use a pivot table.

and btw, this code is giving an error w/ an exclamation mark and a message "400"

thanks anyway

 

[hoadiem]

anyone help me on this, i tried my best and what i could do is, i just able to get one search at a time , trying to loop but if i loop 6 row it will take the last row value and place on every row.

please help thanks a lot