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

Quote from Mumps

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.

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

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?

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

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

it could change from time to time so if you can show me how i can change it when im needed