none
Copy a Range of Data from one sheet to another, IF the Part number is present in both sheets? RRS feed

  • Question

  • I have two files. One get's updated and emailed daily. The other is the "Master" file that I want to add the button/macro to.

    That is when running the Macro in the Master file I want to look through Column B on the daily updated file. If the Part Number is present in both that Column B (Sheet "Status") on the daily file and the Master file (Column H) the paste Columns C-N into the Master File (Sheet "XCHART") starting at Column AK

    DAILY EMAILED FILE:


    MASTER FILE:


    • Edited by stillanoob Monday, October 12, 2015 4:59 PM
    Monday, October 12, 2015 2:33 PM

Answers

  • Hi stillanoob,

    >> I don't see anything looking to match a specific column from file A and file B, and then pasting values if they match

    For matching a specific column from file A and file B, I think you could loop the cells in column B.

    Here is a simple code, you will need to modify it to meet your requirement. You will need to write code to copy the cells.

    Sub CopyRange()
        Dim a As Worksheet
        Dim b As Worksheet
        Dim rng As Range
        'open the workbooks
        Workbooks.Open "D:\OfficeDev\Excel\201510\Master.xlsx"
        Set a = Workbooks("Master.xlsx").Worksheets("Sheet1")    
        Workbooks.Open "D:\OfficeDev\Excel\201510\MasterBak.xlsx"
        Set b = Workbooks("MasterBak.xlsx").Worksheets("Sheet1")
        'loop the cells in column B
        For r = 2 To a.UsedRange.Rows.Count
            If Trim(a.Cells(r, 2)) <> "" Then
                With b.Range("B:B")
                    Set rng = .Find(What:=a.Cells(r, 2), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                    If Not rng Is Nothing Then
                        'write code to copy the cells
                        Debug.Print a.Cells(r, 2)
                    End If
                End With
            End If
        Next
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 13, 2015 6:52 AM

All replies

  • I have two files. One get's updated and emailed daily. The other is the "Master" file that I want to add the button/macro to.

    That is when running the Macro in the Master file I want to look through Column B on the daily updated file. If the Part Number is present in both that Column B (Sheet "Status") on the daily file and the Master file (Column H) the paste Columns C-N into the Master File (Sheet "XCHART") starting at Column AK

    Monday, October 12, 2015 1:24 PM
  • Is this question entirely about Excel?  You've posted it in the Access forum, so unless you're asking about how to automate the Excel application from Microsoft Access, you may do better posting it in the Excel forum.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, October 12, 2015 2:21 PM
  • Yes it is an Excel based problem. I didn't realize I was in the Access section. Sorry.
    Monday, October 12, 2015 2:32 PM
  • Use Match function, it will return a number if the text is found, if the text is not found, it will error.

    From there do a simple if then statement

    =if IFERROR(MATCH(Value,Array,type),0)>0 then

    The Value will need to be entered by you (what text do you want to search by)

    The Array will need to be entered by you (where do you want to look)

    The Type needs to be entered by you (see below)

    1 (default) The MATCH function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order. 
    If the match_type parameter is omitted, it assumes a match_type of 1. 
    0 The MATCH function will find the first value that is equal to value. The array can be sorted in any order. 
    -1 The MATCH function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order 

    Monday, October 12, 2015 3:01 PM
  • Hi Mark thanks for responding. I should add that I'm relatively new to this stuff. Your example above looks like it would only work with data in one file. I don't see anything looking to match a specific column from file A and file B, and then pasting values if they match. 
    Monday, October 12, 2015 4:55 PM
  • Hi stillanoob,

    >> I don't see anything looking to match a specific column from file A and file B, and then pasting values if they match

    For matching a specific column from file A and file B, I think you could loop the cells in column B.

    Here is a simple code, you will need to modify it to meet your requirement. You will need to write code to copy the cells.

    Sub CopyRange()
        Dim a As Worksheet
        Dim b As Worksheet
        Dim rng As Range
        'open the workbooks
        Workbooks.Open "D:\OfficeDev\Excel\201510\Master.xlsx"
        Set a = Workbooks("Master.xlsx").Worksheets("Sheet1")    
        Workbooks.Open "D:\OfficeDev\Excel\201510\MasterBak.xlsx"
        Set b = Workbooks("MasterBak.xlsx").Worksheets("Sheet1")
        'loop the cells in column B
        For r = 2 To a.UsedRange.Rows.Count
            If Trim(a.Cells(r, 2)) <> "" Then
                With b.Range("B:B")
                    Set rng = .Find(What:=a.Cells(r, 2), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                    If Not rng Is Nothing Then
                        'write code to copy the cells
                        Debug.Print a.Cells(r, 2)
                    End If
                End With
            End If
        Next
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, October 13, 2015 6:52 AM