+ Reply to Thread
Results 1 to 18 of 18

Matching Values and Copying to A new sheet.

Hybrid View

  1. #1
    Registered User
    Join Date
    03-24-2008
    Posts
    17
    Yeah I feel like an idiot about that lol.

    Is it possible to have the report format like I was hoping in previous post? Just curious is all.

  2. #2
    Valued Forum Contributor
    Join Date
    09-19-2008
    Location
    It varies ...
    MS-Off Ver
    Office365 - 64bit
    Posts
    862
    Yep - here you go: just make the first paste of the first two columns conditional on booMatchFound being false and remove the sort at the end.
    Option Explicit
    Option Base 1
    
    Sub MatchAndPaste()
    Dim strPartsSheet As String, strDetailSheet As String, strSummarySheet As String
    Dim intFirstDataRow As Integer, intLastDataRow1 As Integer, intLastDataRow2 As Integer
    Dim sht As Worksheet
    Dim strPart As String, strDetail As String, strOrder As String
    Dim intCount As Integer
    Dim booMatchFound As Boolean
    Dim i As Integer, j As Integer
    
        'setup/intialise:
        strPartsSheet = "Sheet1"
        strDetailSheet = "Sheet2"
        strSummarySheet = "Sheet3"
        intFirstDataRow = 2
        
        intLastDataRow1 = Worksheets(strPartsSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count
        intLastDataRow2 = Worksheets(strDetailSheet).Cells(intFirstDataRow, 1).CurrentRegion.Rows.Count
        
        For Each sht In Worksheets
            If sht.Name = strPartsSheet Or sht.Name = strDetailSheet Then
                Worksheets(sht.Name).Select
                Worksheets(sht.Name).Range("A1").Sort _
                    key1:=Worksheets(sht.Name).Columns("A"), order1:=xlAscending, _
                    key2:=Worksheets(sht.Name).Columns("B"), order2:=xlAscending, _
                    header:=xlYes
            End If
        Next sht
        
        intCount = 0
        Worksheets(strSummarySheet).Cells.ClearContents
            
        For i = intFirstDataRow To intLastDataRow1
            strOrder = Worksheets(1).Cells(i, 1).Text
            strPart = Worksheets(1).Cells(i, 3).Text
            strDetail = "NO MATCH"
            For j = 1 To intLastDataRow2
                If Worksheets(1).Range("A" & i) = Worksheets(2).Range("A" & j) Then
                    If Worksheets(1).Range("B" & i) = Worksheets(2).Range("B" & j) Then
                        strDetail = Worksheets(2).Cells(j, 3).Text
                        intCount = intCount + 1
                        If booMatchFound = False Then
                            Worksheets(strSummarySheet).Range("A" & intFirstDataRow + intCount - 1).Value = strOrder
                            Worksheets(strSummarySheet).Range("B" & intFirstDataRow + intCount - 1).Value = strPart
                        End If
                        Worksheets(strSummarySheet).Range("C" & intFirstDataRow + intCount - 1).Value = strDetail
                        booMatchFound = True
                    End If
                End If
            Next j
            'if you get to here, you've cycled through the current record in sheet1 and attempted to
            'match it against all the records in sheet2 and found no match; in this case, need to
            'copy the Order and Part Number to the Summary sheet:
            If booMatchFound = False Then
                intCount = intCount + 1
                Worksheets(strSummarySheet).Range("A" & intFirstDataRow + intCount - 1).Value = strOrder
                Worksheets(strSummarySheet).Range("B" & intFirstDataRow + intCount - 1).Value = strPart
                Worksheets(strSummarySheet).Range("C" & intFirstDataRow + intCount - 1).Value = strDetail
            Else
                booMatchFound = False
            End If
        Next i
        
        With Worksheets(strSummarySheet)
            .Columns("A:C").NumberFormat = "@"
            .Columns("A:C").HorizontalAlignment = xlRight
            .Cells(1, 1).Value = "Order Number"
            .Cells(1, 2).Value = "Part Number"
            .Cells(1, 3).Value = "Detail"
        End With
        
    End Sub
    MatrixMan.
    --------------------------------------
    If this - or any - reply helps you, remember to say thanks by clicking on *Add Reputation.
    If your issue is now resolved, remember to mark as solved - click Thread Tools at top right of thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. macro - copying values between two sheets
    By Wedge120 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-15-2008, 10:45 AM
  2. Sealecting rows by value and copying to new sheet
    By cleaco in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-15-2008, 05:50 AM
  3. Copy values from multiple worksheets onto one master sheet
    By CGBatch in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-18-2007, 04:46 AM
  4. Copying values on update
    By grant606 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2007, 02:44 PM
  5. copying cell values to specific area in sheet
    By Reinder in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-12-2007, 09:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1