Pulling data from another workbook using VBA in excel

Question

I have a range of unique project IDs in 2 workbooks. One of the workbooks doesn't contain the details of the projects, while the other workbook contains all of the project details.

I want to pull out the information from one workbook according to the unique project ID found in the other workbook. I have tried coding this, but it only works for extracting the data from the external workbook for the one with the project ID. I need it to work to extract the data for a range of project IDs given in a column.

This is the code I currently have:

Sub AAA()

    If Workbooks("Source.xlsm").Sheets("Sheet2").Range("A2").Value = Workbooks("Target.xlsm").Sheets("Sheet1").Range("A2").Value Then
        Workbooks("Source.xlsm").Sheets("Sheet2").Range("B2").Value = Workbooks("Target.xlsm").Sheets("Sheet1").Range("C2").Value
    End If

End Sub

This code only works for a particular cell, but I would need to extract a range of data from a range of project IDs located in the external workbook. What can I do to get this to work?

This sounds like the type of request for which the built-in VLOOKUP function works. Have you considered using this non-VBA approach? If so, can you tell us why it didn't work? — basodre
– basodre, Commented Aug 6, 2015 at 14:22

Nitish · Accepted Answer · 2015-08-12 10:02:18Z

3

As per your question you need to add one more loop try this

Sub copydata()
Dim i As Long, j As Long
Dim targetlastrow As Long, sourcelstrow As Long
Dim Sourcelastcol As Long
Dim source As Worksheet
Dim target As Worksheet

Set source = Workbooks("Source.xlsm").Sheets("Sheet2")
Set target = Workbooks("Target.xlsm").Sheets("Sheet1")

targetlastrow = target.Range("A" & target.Rows.Count).End(xlUp).Row
sourcelstrow = source.Range("A" & source.Rows.Count).End(xlUp).Row
Sourcelastcol = source.Cells(2, source.Columns.Count).End(xlToLeft).Column

For i = 2 To targetlastrow
    For j = 2 To sourcelstrow
        If target.Range("A" & i).Value = source.Range("A" & j) Then
            source.Activate
            source.Range("B" & j).Select
            Range(ActiveCell, ActiveCell.Offset(0, Sourcelastcol)).Copy
            target.Range("B" & i).PasteSpecial
        End If
    Next j
Next i
End Sub

edited Aug 12, 2015 at 10:02

answered Aug 6, 2015 at 14:44

Nitish

3411 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

nabilah Over a year ago

Hi! Not copy the entire row but based on their project ID. So if in source workbook contain that project ID, the details will be copied to the target workbook based on the same project IDs. I have tried your coding but it seems to only read ine project ID and copied it to the whole range which is not what i was looking for. I hope you could assist me

Nitish Over a year ago

HI! it seems you are trying to copy only particular columns in source book and paste them into target. if that is right please provide which column has to copy from source and in which column it has to be pasted in target. or you can change the code for your convenient

nabilah Over a year ago

However I would also need the code to run through each row to match the other Project IDs found in the other workbook. The codes above only works if both Project IDs as in the same row. Is there a code that I could use to look through all the rows of column that contain the Project IDs from both the Workbooks? @Nitish

Nitish Over a year ago

Hi! for that you need to add one more loop I have updated the above answer check that

nabilah Over a year ago

Hi i already used the code that you have updated. it works! thank you! However i have one more problem, i realize that my columns for both workbook is not the same. For example header column C in "Target" workbook is found in header column D in "Source" workbook. Thus if i were to do the code you gave, it will copy and paste the information in the wrong column. Is there any idea that i could use to copy it correctly? @Nitish

|

Collectives™ on Stack Overflow

Pulling data from another workbook using VBA in excel

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related