CODEWITHSUNDEEP
vbaexcel
user1520860
user1520860

Reputation: 71

Moving data from one sheet to another in Excel.

I am trying to write code in VBA that gets data from another workbook and pastes it in the right place. For example:

In one workbook:

123 x  
321 xx  
159 xxx  
235 xxxx

The x column should be copied and placed in the right place to end up like this:

235    xxxx
321    xx
123    x
159    xxx

I have already managed to get the data, but the problem is when I try to put it in the right cell; the If statement in the For...Next block does not work properly. Here is my code:

Sub copyToWorkbook()
    Dim arr(32) As String
    Dim rr(32) As String
    Dim temp(32) As String

For k = 1 To 32
    temp(k) = Cells(k, 1).Value
Next
With ActiveSheet
        .Range("B200:B300").FormulaArray = "='" & "g:" & "\[" & "PPM_et_top_fournisseur.xls" & "]" _
                    & "PPM officiels" & "'!" & "B12:B43"
End With
Dim q As Integer
For f = 1 To 32
    q = 199 + f
    arr(f) = Cells(q, 2).Value
    Cells(f, 8) = arr(f)
Next

With ActiveSheet.Range("C200:C300")
     .FormulaArray = "='" & "g:" & "\[" & "PPM_et_top_fournisseur.xls" & "]" _
                    & "PPM officiels" & "'!" & "J12:J43"
     .Value = .Value
End With

Dim w As Integer

For o = 1 To 32
    w = 199 + o
    rr(o) = Cells(w, 3).Value
    Cells(o, 9) = rr(o)
Next
For t = 1 To 32
    For n = 1 To 32
        If Cells(20, 1).Value <> arr(n) Then
           Cells(n, 7) = arr(n)
            Else: Cells(t, 10) = "nop"
        End If
    Next
Next
End Sub

Upvotes: 0

Views: 2145

Answers (1)

danielpiestrak
danielpiestrak

Reputation: 5439

This looks like you're trying to use VLOOKUP.

However, if you really wanted to accomplish this in VBA, I'd recommend loading the source book into a two dimensional array, then looping through the array to output its values to the destination sheet in the appropiate rows.

I answered a similar question earlier this week here: EXCEL VBA Paste from array, change paste order

The only real difference it seems is that person was using entire columns of data, and rearranging horizontally instead of vertically.

That being said, it really sounds like you can just use the VLOOKUP formula to lookup the x column values based on the first column.

Upvotes: 1

Related Questions