#1
|
|||
|
|||
Copy data from spreadsheets to a two, with the sum criterion
I'd like to take the data entered in the spreadsheet called "Base_1" and "Base_2" and was placed on the worksheet called "Summary".
But the data that has the same "Naterial" on "Base_1" and "Base_2" were to "Summary" with the amounts added. Thank you! See Appendix |
#2
|
|||
|
|||
Thank's !!
im solved in http://www.excelforum.com/excel-prog...91#post2790691 Code:
Sub ASSummary() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rngBase1 As Range Dim rngBase2 As Range Dim rngCell As Range Dim lngQty As Long Set ws1 = ActiveWorkbook.Sheets("Base_1") Set ws2 = ActiveWorkbook.Sheets("Base_2") Set wsSum = ActiveWorkbook.Sheets("Resumo") With Application .ScreenUpdating = False .EnableEvents = False End With With ws1 Set rngBase1 = .Range("B2", .Range("B2").End(xlDown)) End With With ws2 Set rngBase2 = .Range("B2", .Range("B2").End(xlDown)) End With For Each rngCell In rngBase1 lngQty = rngCell.Offset(0, -1).Value Set rngFound = ws2.Columns(2).Find(What:=rngCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows) If Not rngFound Is Nothing Then lngQty = lngQty + rngFound.Offset(0, -1).Value End If With wsSum Set rngDest = .Range("A2000").End(xlUp).Offset(1) End With rngDest.Value = lngQty rngDest.Offset(0, 1) = rngCell rngDest.Offset(0, 2) = rngCell.Offset(0, 1) Next For Each rngCell In rngBase2 Set rngFound = ws1.Columns(2).Find(What:=rngCell, LookIn:=xlValues, LookAt:= _ xlPart, SearchOrder:=xlByRows) If rngFound Is Nothing Then With wsSum Set rngDest = .Range("A2000").End(xlUp).Offset(1) End With rngCell.Offset(0, -1).Resize(1, 3).Copy rngDest.Resize(1, 3).PasteSpecial xlPasteValues End If Next With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can't open spreadsheets | kaygeea | Excel | 7 | 10-05-2011 08:00 AM |
Merging two spreadsheets | BrazzellMarketing | Excel | 5 | 04-26-2011 01:51 PM |
Finding the difference of two dates based on criterion. | aligahk06 | Excel | 0 | 04-27-2010 12:12 AM |
Analysis of Amount based on following criterion. | aligahk06 | Excel | 1 | 04-24-2010 10:34 AM |
PWA, sharepoint links, and spreadsheets | Rkramkowski | Project | 0 | 06-21-2006 01:29 PM |