VBA help to transfer additional data to ws

[steve400243]

Hello Forum, I use this code to copy data over from ws2 to ws1 when cell C3 is updated.
From ws2 I need the data in column "H" (10) to copy to ws1 column "N" (13) and I cannot figure out how to make this addition. I tried adding in "sn(i, 13) = arr(x, 10)" But this does not work. Thank you for all help, or advise.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
    Range("A13", "H34").ClearContents
    Dim ws1 As Worksheet, ws2 As Worksheet, lr As Long, arr As Variant, sn As Variant
    Set ws1 = Sheets("CFS SHEET")
    Set ws2 = Sheets("DATA")
    lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
    arr = ws2.Range("A2", "I" & lr)
    ReDim sn(UBound(arr), 8)
    i = 0
    For x = 1 To UBound(arr)
        If arr(x, 1) = Range("C3") Then
            sn(i, 0) = arr(x, 2)
            sn(i, 1) = arr(x, 3)
            sn(i, 2) = arr(x, 4)
            sn(i, 3) = arr(x, 5)
            sn(i, 4) = arr(x, 6)
            sn(i, 5) = arr(x, 7)
            sn(i, 6) = arr(x, 8)
            sn(i, 7) = arr(x, 9)
            
            i = i + 1
        End If
    Next
    If i > 0 Then ws1.Range("A13").Resize(i, 8) = sn
End If
End Sub

 

[Fluff]

Col H is col 8 & col N is 14, so do you want cols H to N or J to M?

 

[steve400243]

Col H is col 8 & col N is 14, so do you want cols H to N or J to M?

Hi Fluff, thank you for looking at this! Sorry, my typo - I need column J to N

 

[Fluff]

Ok, how about

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
    Range("A13", "H34").ClearContents
    Dim ws1 As Worksheet, ws2 As Worksheet, lr As Long, arr As Variant, sn As Variant
    Set ws1 = Sheets("Data")
    Set ws2 = Sheets("pcode")
    lr = ws2.Range("A" & Rows.Count).End(xlUp).Row
    arr = ws2.Range("A2:J" & lr)
    ReDim sn(UBound(arr), 14)
    i = 0
    For x = 1 To UBound(arr)
        If arr(x, 1) = Range("C3") Then
            sn(i, 0) = arr(x, 2)
            sn(i, 1) = arr(x, 3)
            sn(i, 2) = arr(x, 4)
            sn(i, 3) = arr(x, 5)
            sn(i, 4) = arr(x, 6)
            sn(i, 5) = arr(x, 7)
            sn(i, 6) = arr(x, 8)
            sn(i, 7) = arr(x, 9)
            sn(i, 13) = arr(x, 10)
            i = i + 1
        End If
    Next
    If i > 0 Then ws1.Range("A13").Resize(i, 14) = sn
End If
End Sub

 

[steve400243]

Thank you Fluff I appreciate your help! That did it, after I changed the ws1, and ws2 names back to the correct.

 

[Fluff]

You're welcome & thanks for the feedback