Thread: Find a record on one excel file based on the value in another file.

Hi Arlette,

There are several cells requiring data transfer to Sheet 2, however I put in my code two of these cells (listed below also) that could be used as samples for now and then I could add the remaining myself.

If however it is easier for you to show me how the code works and where I have gone wrong by me giving you all the cell references I am happy to do this also.

Cell name 'Stage1BuildingCosts' of BPC Form (1st Form), to be copied to Column H of 2nd Form
Cell name 'TopContractor' of BPC Form (1st Form), to be copied to Column O of 2nd Form

As explained in my first email, both to be copied to row where Archdiocesan Job No (Column A) of 2nd Form matches - Cell Ref 'Arch No' from BPC Form (1st Form)

Thank you for replying it is greatly appreciated.
Please let me know if I have not clearly explained this or you do want all the cell references that require transfer.

Kind Regards
Steve

I can see a few problems with your code:

'Stuck from here onwards, please help.....

        Application.ScreenUpdating = False
        Set SrcBook = ThisWorkbook

        On Error Resume Next
        Set DestBook = Workbooks.Open("C:\Users\stevet\BPC Projects Datas.xlsx")


        Dim CompareRange As Variant, x As Variant, y As Variant, Seletion As Variant
        ' Set CompareRange equal to the range to which you will
        ' compare the selection.
        Set CompareRange = DestBook.Sheets("Sheet1").Range("A65536").End(xlUp).Row 
As written, the variable CompareRange will return an integer, not a range.
This will cause an error when attempting to run the For each y loop below.

        Set Selection = SrcBook.Sheets("BPC Form").Range("ArchNo").Value 
Again, as written, this variable will not return a range, causing the For each x loop to error.
Also, it is very poor practice to use the name of a VBA function, method or property as a variable name.

        ' NOTE: If the compare range is located on another workbook
        ' or worksheet, use the following syntax.
        ' Set CompareRange = Workbooks("Book2"). _
          '   Worksheets("Sheet2").Range("C1:C5")
        '
        ' Loop through each cell in the selection and compare it to
        ' each cell in CompareRange.
        For Each x In Selection
            For Each y In CompareRange
                If y = x Then
                    DestBook.Worksheets(1).Range("H").Value = SrcBook.Sheets("Stage1BuildingCosts").Range("ArchNo").Value
                    DestBook.Worksheets(1).Range("O").Value = SrcBook.Sheets("TopContractor").Range("ArchNo").Value
                    'More values will need transfering but the above are just 2 samples to use to show what I am trying to do.
                Next y
            Next x

        End If
        'End Sub


        'End If

        'ActiveSheet.Protect Contents:=True
    End Sub

HTH,
Rich

Hi Rich,

Thank-you so much for your help. Up until Tuesday just gone I was still scratching my head trying to understand where and why my code was falling down.
Thank-you also to other forum members who offered advice and suggestions.
I will now mark my question as solved and rate accordingly.

Another quick question if you don't mind and if you are able to help me with please.

I have wrote code to insert a pdf file into my worksheet as an attachment......and not so much link, however place it in a cell and resize it.
I have also wrote code to save my excel file as a pdf file.
However, is it possible to have code produce the following as I am struggling to put the 2 together and a little more besides: -

On the click of a button, save the current excel sheet I am working on as a pdf file, open up another workbook and copy or transfer this newly created pdf file into this workbook.
So not so much after transferring/copying individual cells of data, more want to transfer/copy the whole excel file into a new workbook as a pdf attachement.

Hope this makes some sort of sense.

Kind Regards
Steve

Sorry, not much experience in this area. Open a new thread and post your existing code. I'm sure someone will give it a go.