Copying row with corresponding ID number into a different worksheet

Question

I have a userform that lets you type in an ID number, I want the row with the corresponding ID number to be copied into a different worksheet where the cells are not aligned.

I have tried using an array for the destination of the data but it doesn't do anything when I run it. I know I could do it the ugly way and code every single cell to go to another cell but I thought using an array for the data that is being copied would work, now I need to know how I can put all the destination cells in one varable and copy the data in there

Dim ws As Worksheet
Set ws = Sheets("Arkiv")
Dim DN As Worksheet
Set DN = Sheets("DN")

idRow = Sheets("Arkiv").Columns("A:A").Find(what:=IDnum).Row
IDnum = TextBox1.Text

'Dim shipFrom As Range
    'Set shipFrom = Sheets("Arkiv").Range("B" & idRow)

Dim goTo1 As Variant
goTo1 = Array(DN.Range("D9"), DN.Range("E9"), DN.Range("I9"), DN.Range("C20"), DN.Range("D20"), DN.Range("E45"), DN.Range("G20"), DN.Range("H20"), DN.Range("I20"))

Dim data As Variant
data = Array(ws.Range("B" & idRow), ws.Range("C" & idRow), ws.Range("D" & idRow), ws.Range("E" & idRow), ws.Range("F" & idRow), ws.Range("G" & idRow), ws.Range("H" & idRow), ws.Range("I" & idRow))

goTo1 = data

I expect the data from the variable "data to be copied over to the cells inside of the "goTo1" variable in the corresponding order I put them into the array with.enter code here

Answer 1

1. If you use the find method always specify the LookAt patameter, otherwise VBA uses whatever was used last time (by either a user or VBA).

2. You need to loop through the addresses, and copy them one by one. You cannot copy non-coninous ranges at once.

So something like this should work.

Option Explicit

Public Sub CopyRanges()
    Dim wsSource As Worksheet
    Set wsSource = ThisWorkbook.Worksheets("Arkiv")
    Dim wsDestination As Worksheet
    Set wsDestination = ThisWorkbook.Worksheets("DN")

    Dim IDnum As String
    IDnum = TextBox1.Text

    Dim idRow As Long
    idRow = wsSource.Columns("A:A").Find(What:=IDnum, LookAt:=xlWhole).Row

    Dim SourceAddresses() As Variant
    SourceAddresses = Array("B" & idRow, "C" & idRow, "D" & idRow, "E" & idRow, "F" & idRow, "G" & idRow, "H" & idRow, "I" & idRow)

    Dim DestinationAddresses() As Variant
    DestinationAddresses = Array("D9", "E9", "I9", "C20", "D20", "E45", "G20", "H20", "I20") 

    If UBound(SourceAddresses) <> UBound(DestinationAddresses) Then
        MsgBox "Amount of source addresses must be the same amount as destination addresses"
        Exit Sub
    End If     

    Dim i As Long
    For i = LBound(SourceAddresses) To UBound(SourceAddresses)
        wsDestination.Range(DestinationAddresses(i)).Value = wsSource.Range(SourceAddresses(i)).Value
    Next i
End Sub