Searching for an exact match between open workbook and closed workbook

  • Afternoon All

    Firstly please excuse my ignorance as i am very new to VBA and MACRO's.


    My issue is that i have 2 workbooks (workbook 1Which is closed & workbook 2 which would be open) Workbook 1 has 5 worksheets with different names i.e FY15,FY16 etc the second workbook has 2 worksheets with the first one containing the base data for the second worksheet. (the data is copied across automatically in to the second worksheet.


    I want to add a number or text in to Cell D5 in Workbook 2,Worksheet 1 and search the entire workbook 1 for the same number that is in D5, if it returns with a match I would then like to copy a range of 80 cells i.e C8:F26 which is related to that number from workbook 1 and copy them to workbook 2, worksheet 1 to a specific location i.e. B7:E26


    The final issue is the numbers i am searching for can appear in various different locations in the workbook 1 across all the 5 different worksheets, the cells i want to copy will always be in the same location compared to the searched numbered cell I.e. starting at 3 cells down and ending 23 cells down and 4 cells across from the reference numbered cell.


    I am struggling to know where and how to start. My past experience of VBA coding has been to use the record macro function to do actions within one workbook and doesn't include searching for matches etc. any help will be greatly appreciated


    Many thanks in advance

    Rich

  • It would be easier to help and test possible solutions if you could attach copies of both workbooks (de-sensitized if necessary) and explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • It would be easier to help and test possible solutions if you could attach copies of both workbooks (de-sensitized if necessary) and explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

    thanks Mumps


    I will attach some files when I get to work tomorrow and try to explain what I want to do

  • Sorry for the delay had to have an impromptu day from work so dint have access to the files. Anyway attached are 2 workbooks as i described i'm my original post.


    What i would like to do is add a number in to the workbook "invoice Tracking"on either worksheets "F100 Detail - design" and the "F100 Detail - Construction" in to the cells D6,H6,L6,P6,T6 or X6

    I would then like to run a macro which will search the other workbook "F100 Tracking" (which would not be open at the time) for the number matches which would be in cells C5 or C32 ,H5 or H32,M5 or M32 on any of the worksheets within the workbook.(Fy15 - FY20)


    Once it has found the number match i would then like it to copy the data in the block of cell under the the relevant number i.e. B10:E27 (iof the searched for number is in the first box C5), and copy this back to the other worksheet (which would be open) and place the details in the corresponding group of cells linked to the searched number.i.e first box B8:E27.


    Finally i would like it to also insert the cells C6, E5 and E6 to the Invoice tracking worksheet but put these in to it corresponding cell which should be C6 goes to D30, E5 goes to D29 and E6 goes to D31,(that's if the searched number is in the first group.


    I have tried to do it using an offset (i.e. the cells are always in the same place in relation to the searched for number but it just didnt work they way i would like. so had to scrap the whole lot as it got corrupted somehow.


    I know its quite complex and i might not even be able to do what i would like but if you could help it would save me hrs and hrs of hand jamming figures and detail in to 2 separate sheets.


    Many thanks in advance.

  • What is the full path to the folder containing the "F100 Tracking" workbook?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • F100 tracking is R:\CENMP\CENMPRW\Work in Progress

    Invoice tracking is R:\CENMP\CENMPRW\Work in Progress


    How ever i will move these once tested but i can change the address once ive moved it


    Thanks again Mumps

  • Try the attached file. I have added two buttons in the 2 sheets in question. One will open the F100 Tracking file and the other will close it when you are done with it. The macros assume that the F100 Tracking file has been saved in the same folder as the Invoice Tracking file. If you move the files to a different folder, that won't make any difference as long as the 2 files are in the same folder. Click the "Open" button to open the file. Next enter a value in a cell in Range("D6, H6, L6, P6, T6, X6") and press the RETURN key. To make it easier, you could add a drop down list of all your F100 ID No's into the cells in Range("D6, H6, L6, P6, T6, X6") and then simply select the number from the drop down. If you prefer not to click the buttons to open and close the file, we could have the F100 Tracking file opened automatically when you open then Invoice file and close the F100 file when you close the Invoice file. If that would work better for you, please let me know and I will make the necessary changes. Please note that in the macros I have used "F100 Tracking.xlsx" as the name of the file. You can change that name in the macros to suit your needs. The macros are in Module 1 and also in the worksheet code modules for the 2 sheets in question.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks so much mumps I’ll have a look tomorrow when I’m back at work. I might need the file to be in different locations as the tracking file is a finance document and the invoice is our project one, different departments but I’ll let you know if I need it to be changed


    so impressed it did t take you long to do as well I must learn this stuff.

  • Good Morning Mumps


    I have download the file you attached but get the following message when loading it up


    "Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.

    Removed Part: /xl/printerSettings/printerSettings3.bin part. (Print options)"


    and the Macro is then not there are you able to add it as a text file and i will then copy and make a new macro ( it could be my office firewall that isn't happy to down load off the forum?

  • I have attached the file again for you to try. Make sure that macros are enabled in Excel. I have also attached the macros below.

    Copy and paste this macro into the worksheet code modules for F100 Detail - Design and F100 Detail - Construction. Do the following: right click the tab name for the F100 Detail - Design sheet and click 'View Code'. Paste the following macro into the empty code window that opens up. Close the code window to return to your sheet. Repeat this for F100 Detail - Construction so that the macro is in both worksheet code modules.

    Place the following 2 macros in a regular module:

    Code
    1. Sub OpenF100Tracking()
    2. Dim desWS As Worksheet
    3. Set desWS = ThisWorkbook.ActiveSheet
    4. Workbooks.Open ThisWorkbook.Path & "\" & "F100 Tracking.xlsx"
    5. desWS.Activate
    6. End Sub
    7. Sub CloseF100Tracking()
    8. Workbooks("F100 Tracking.xlsx").Close False
    9. End Sub

    Assign the first macro to an "Open" button and the second to a "Close" button on the sheet.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps


    I have followed both the attachment and adding it in as a new macro the work sheet opens and closes fine but when i hit enter after putting my number in i.e 15-00048 it just moves down to the next line and doesn't fill in the details?

  • My Apologies it does work great just that i have to have the window physically showing for it to pull the data across, Is there any way that it can search for the numbers and data even if the F100 Tracking work book is closed but still in its same file location

  • I'm sure there is but I'm not familiar with the process. Have a look at Carim's suggestion in Post #3. Is there any reason why you don't want the F100 Tracking workbook to be open?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • The eventual plan is to have it as a one sheet for all projects not just mine. So the file would need to be used by several people. Have to have the file open could cause issues if somebody else is working in it and I can then not gather the information. Unless it will still work if the file is “read Only”. Thanks for your help it looked a simple ish script in the end. Very much appreciated thanks Mumps.

  • Hi Mumps


    I have had to change the location of the reference file F100 Tracker to a new location, How do i edit the VBA and links as i don't seam to be able to edit the link and it save the new location. The F100 Tracking File will now be in a different location than the invoice tracking sheet. Will i need to change the whole script to do this?


    attached id the script you did with a few slight tweaks for extra information that i needed to capture and a layout change i made.



    Private Sub CommandButton1_Click()

    Sub OpenF100Tracking()

    Dim desWS As Worksheet

    Set desWS = ThisWorkbook.ActiveSheet

    Workbooks.Open ThisWorkbook.Path & "\" & "F100 Tracking.xlsx"

    desWS.Activate

    End Sub

    End Sub


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("C5, G5, K5, O5, S5, W5")) Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    Dim srcWB As Workbook, desWS As Worksheet, ws As Worksheet, fnd As Range

    Set srcWB = Workbooks("F100 Tracking.xlsx")

    Set desWS = ThisWorkbook.ActiveSheet

    For Each ws In srcWB.Sheets

    Set fnd = ws.Range("C5, C32 ,H5,H32,M5,M32").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)

    If Not fnd Is Nothing Then

    fnd.Offset(3, -1).Resize(21, 4).Copy Target.Offset(2, -1)

    Target.Offset(25, 1) = fnd.Offset(1)

    Target.Offset(24, 1) = fnd.Offset(, 2)

    Target.Offset(26, 1) = fnd.Offset(1, 2)

    Target.Offset(-1, 0) = fnd.Offset(-1, 0)

    End If

    Next ws

    Application.ScreenUpdating = True

    End Sub

  • Will the file location be changing from time to time or is it now fixed? If it is fixed, what is the full path to the folder containing the file?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • How about having a pop up window each time asking you to select the file? Although it may be tedious being asked each time the macro runs, this will save you from having to change the macro each time the location changes. Will that work for you?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.