Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-15-2012, 04:45 AM
marreco marreco is offline Copy data from spreadsheets to a two, with the sum criterion Windows XP Copy data from spreadsheets to a two, with the sum criterion Office 2007
Novice
Copy data from spreadsheets to a two, with the sum criterion
 
Join Date: Mar 2012
Posts: 8
marreco is on a distinguished road
Default 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
Attached Files
File Type: xlsx CopydataSum.xlsx (10.1 KB, 9 views)
Reply With Quote
  #2  
Old 05-15-2012, 04:15 PM
marreco marreco is offline Copy data from spreadsheets to a two, with the sum criterion Windows XP Copy data from spreadsheets to a two, with the sum criterion Office 2007
Novice
Copy data from spreadsheets to a two, with the sum criterion
 
Join Date: Mar 2012
Posts: 8
marreco is on a distinguished road
Default

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
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy data from spreadsheets to a two, with the sum criterion Can't open spreadsheets kaygeea Excel 7 10-05-2011 08:00 AM
Copy data from spreadsheets to a two, with the sum criterion 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:05 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft