Transfer Data between sheets ...

[dreid1011]

Ok, this is a continuation from my thread yesterday. (That issue was solved http://www.mrexcel.com/forum/excel-questions/859336-transfer-data-sheet1-sheet2.html) Now, I am trying to use some arrays for the transfer, but I am receiving an error stating my Range has failed.

The *** is the line in question.

It works just fine if I hard code the range.

Sub Record_Click()
    Dim dataIn(0 To 9) As Range
    Dim dataOut(0 To 9) As String
    Dim i As Integer
    i = 0
    Set dataIn(0) = Range("B5")
    Set dataIn(1) = Range("D5")
    Set dataIn(2) = Range("B8")
    Set dataIn(3) = Range("F8")
    Set dataIn(4) = Range("M22")
    Set dataIn(5) = Range("D11")
    Set dataIn(6) = Range("M23")
    Set dataIn(7) = Range("M24")
    Set dataIn(8) = Range("B14")
    Set dataIn(9) = Range("D14")
    dataOut(0) = "B"
    dataOut(1) = "C"
    dataOut(2) = "D"
    dataOut(3) = "E"
    dataOut(4) = "F"
    dataOut(5) = "G"
    dataOut(6) = "H"
    dataOut(7) = "I"
    dataOut(8) = "J"
    dataOut(9) = "K"
    'sets the values from the comboboxes to specific cells to be copied to sheet2
    Range("M22").Value = cboLetter.Value
    Range("M23").Value = cboExAmt.Value
    Range("M24").Value = cboNew.Value
    'finds the last row in column B and moves one row down to the next empty row
    lr = Worksheets("Vet List").Range("B" & Rows.Count).End(xlUp).Row + 1
    'copies data to each specified column in the row identified above
 ***Range(dataIn(i)).Copy Worksheets("Vet List").Cells(lr, dataOut(i))
    'Range("D5").Copy Worksheets("Vet List").Cells(lr, "C")
    'Range("B8").Copy Worksheets("Vet List").Cells(lr, "D")
    'Range("F8").Copy Worksheets("Vet List").Cells(lr, "E")
    'Range("M22").Copy Worksheets("Vet List").Cells(lr, "F")
    'Range("D11").Copy Worksheets("Vet List").Cells(lr, "G")
    'Range("M23").Copy Worksheets("Vet List").Cells(lr, "H")
    'Range("M24").Copy Worksheets("Vet List").Cells(lr, "I")
    'Range("B14").Copy Worksheets("Vet List").Cells(lr, "J")
    'Range("D14").Copy Worksheets("Vet List").Cells(lr, "K")
    MsgBox "Record Added"
    'calls Reset_Click() to clear the fields
    'Call Reset_Click
End Sub

 

[dreid1011]

Problem solved. I was passing the wrong data type to Range.