Posts by Richwhite010

  • Searching for an exact match between open workbook and closed workbook

    Thanks Mumps

    The boss didn't like the box as it had to be done after entering the F100 ID no in each time. Thanks again for your help. I'm going to have a play around over the next few days to see if i can get the script to work off another button. i.e. so i can enter all the f100 id no is on all fields and then hit the button to fetch the data for all the entries. Much appreciated once again its working a treat so far.

  • Searching for an exact match between open workbook and closed workbook

    sorry since i have shown the others what you have made so far they have loved the idea but now want different hings than i had originally wanted. I am trying to get them to use a master sheet for the F100 tracker which will then be in one place for all to use? If thats the case it will only be one more change if not (i.e each want there own) i will need to alter the script for each person.

  • Searching for an exact match between open workbook and closed workbook

    id prefer to have the buttons as that works well for the office, It wont be changing often maybe once or twice by the end of the year, if i could learn how to do it that would be far better in case i need to build one for each and every project engineer. Once they are set up on there account or pathway the main file for them i.e. F100 tracking ### (initials) will be allocated to them. I can change the file search id easy enough that's self explanatory but the link and the opening of the file is proving difficult to modify or at-least for me to find where i need to modify it.

  • Searching for an exact match between open workbook and closed workbook

    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

  • Searching for an exact match between open workbook and closed workbook

    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.

  • Searching for an exact match between open workbook and closed workbook

    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?

  • Searching for an exact match between open workbook and closed workbook

    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.

  • 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