CODEWITHSUNDEEP
excelvba
Cakradewa Hjj
Cakradewa Hjj

Reputation: 23

Get Value from another file

I want to get Values from another workbook based on No.Id and insert a new column between columns.

For example, I have data like this:
1st workbook is on the left and 2nd Workbook on the right is.
enter image description here

On 2nd Workbook I want to insert a new column between column and put the values based on No.Id after click on CommandButton1.

So the 2nd Workbook looks like this after macro
enter image description here

Upvotes: 2

Views: 2008

Answers (1)

InternInNeed
InternInNeed

Reputation: 159

Try:

Dim wsSource As Worksheet
Set wsSource = ActiveWorkbook.Worksheet("NAME SOURCE SHEET")
Dim wsOutput As Worksheet
Set wsOutput = ActiveWorkbook.Worksheet("NAME OUTPUT SHEET")
Dim ID as Range
Dim FindID as Range
Dim lRowSource as Integer
Dim lRowOutput as Integer

lRowSource = wsSource.Range("A" & Rows.Count).End(xlUp).row
lRowOutput = wsSource.Range("A" & Rows.Count).End(xlUp).row

With wsOutput
    Range("B1").EntireColumn.Insert

    For each ID in .Range("A1:A" & lRowSource)
    Set FindID = wsSource.Range("A1:A" & lRowOutput).Find(What:=ID, LookIn:=xlValues, lookat:=xlWhole)

    If Not FindID is Nothing then
        wsSource.Range("B" & FindID.Row).Copy Destination:=wsOutput.Range("B" & FindID.Row)
    Else
        Exit Sub
    End If
End With

Or you can also use:

Dim wsSource As Worksheet
Set wsSource = ActiveWorkbook.Worksheet("NAME SOURCE SHEET")
Dim wsOutput As Worksheet
Set wsOutput = ActiveWorkbook.Worksheet("NAME OUTPUT SHEET")
Dim i As Long

wsOutput.Range("B1").EntireColumn.Insert

For i=1 To wsSource.UsedRange.Rows.Count
    If wsSource.Range("A" & i) = wsOutput.Range("A" & i) Then
        wsSource.Range("B" & i).Copy Destination:=wsOutput.Range("B" & i)
    End If
Next i

Upvotes: 1

Related Questions