I am trying to get the four values in Range("D11,D13,I20,D15") of source workbook and sheet1 to target workbook and sheet ORDER FORM range(A41)up next available row, each in individual cells of that row and in the order they are in.

Two things happen when I run the code.

1. All the Range("D11,D13,I20,D15") cells values are replaced with 1. I have tried text and numbers in these cell to start. All are 1's after code runs.

2. Type mismatch error pop up follows.

(I have a function above the code to make the workbook open line work and have tested it, works fine.)

I have rechecked the sheet names and book names and those seem to be in order.

I'm quite shakey on the array and the transpose and I need paste value as the values to be copied are from formulas.

Thanks.
Howard

Option Explicit

Sub PriceToPOrder()

Dim wksSource As Worksheet, wksTarget As Worksheet
Dim wkbSource As Workbook, wkbTarget As Workbook
Dim rngSource As Range, rngTarget As Range
Dim varRicho As Variant
Dim varLanier As Variant

Set wkbSource = Workbooks("PRICE COMPARE TEST SHEET.xlsm")
Set wkbTarget = Workbooks("ORDER FORM TEST SHEET.xlsm")
Set wksSource = wkbSource.Sheets("Sheet1")
Set wksTarget = wkbTarget.Sheets("ORDER FORM")

If Not IsFileOpen("C:\Users\Howard Kittle\Documents\ORDER FORM TEST SHEET.xlsm") Then
Workbooks.Open ("C:\Users\Howard Kittle\Documents\ORDER FORM TEST SHEET.xlsm")
End If

'Set all the copy cells for RICHO or LANIER
varRicho = Array(1, 2, 3, 4)
wkbSource.Sheets("Sheet1").Range("D11,D13,I20,D15" ).Value = varRicho
wkbTarget.Sheets("ORDER FORM").Range("A41" & Rows.Count).End(xlUp)(2) _
.PasteSpecial Paste:=xlPasteValues = Application.Transpose(varRicho)


'varLanier = Array(1, 2, 3, 4)
'wkbSource.Sheets("Sheet1").Range("O11,O13,O20,O15 ").Value = varLanier
'wkbTarget.Sheets("Sheet1").Range("A41" & Rows.Count).End(xlUp)(2) _
' .PasteSpecial Paste:=xlPasteValues = Application.Transpose(varLanier)
'Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

End Sub