Cell values to an array, then array transpose to another workbook

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

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
 
Hi Howard,

Am Thu, 16 Jan 2014 03:32:03 -0800 (PST) schrieb L. Howard:
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.
Click to expand...

try:

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(3) As Variant
Dim varLanier As Variant
Dim rngC As Range
Dim i As Long

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

For Each rngC In wksSource.Range("D11,D13,I20,D15")
varRicho(i) = rngC.Value
i = i + 1
Next

wksTarget.Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(columnsize:=4) = varRicho

End Sub


Regards
Claus B.
 
I dimmed rngC as Variant and i as Long.
Code is in standard module.

Type mismatch on vaRicho, yellowed out and hover cursor over it.

varRicho(i) = rngC.Value

Howard
 
Hi Howard,

Am Thu, 16 Jan 2014 05:15:03 -0800 (PST) schrieb L. Howard:
I dimmed rngC as Variant and i as Long.
Code is in standard module.

Type mismatch on vaRicho, yellowed out and hover cursor over it.
Click to expand...

try:
Dim varRicho(3) As Variant
Dim rngC As Range
Dim i As Long

Here it works without errors


Regards
Claus B.
 
Hi Howard,



Am Thu, 16 Jan 2014 05:15:03 -0800 (PST) schrieb L. Howard:







try:

Dim varRicho(3) As Variant

Dim rngC As Range

Dim i As Long



Here it works without errors





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Yes, that did it, works great.


I just need to modify this line to start at row 44 and look up from there. The window it has to copy to is row 44 up to row 29.

wksTarget.Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(columnsize:=4) = varRicho

Howard
 
Hi Howard,

Am Thu, 16 Jan 2014 11:45:16 -0800 (PST) schrieb L. Howard:
wksTarget.Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(columnsize:=4) = varRicho
Click to expand...

try:
wksTarget.Cells(49, 1).End(xlUp)(2).Resize(columnsize:=4) = varRicho

I changed the code to make it better readable:

Sub PriceToPOrder()

Dim wksSource As Worksheet, wksTarget As Worksheet
Dim wkbSource As Workbook, wkbTarget As Workbook
Dim rngSource As Range, rngTarget As Range, rngRicho As Range
Dim varRicho() As Variant
Dim varLanier() As Variant
Dim rngC As Range
Dim i As Long

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")
Set rngRicho = wksSource.Range("D11,D13,I20,D15")

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
ReDim Preserve varRicho(rngRicho.Cells.Count - 1)
For Each rngC In rngRicho
varRicho(i) = rngC.Value
i = i + 1
Next

wksTarget.Cells(49, 1).End(xlUp)(2).Resize(columnsize:=4) = varRicho

End Sub


Regards
Claus B.
 
Hi Howard,

Am Thu, 16 Jan 2014 21:00:38 +0100 schrieb Claus Busch:
wksTarget.Cells(49, 1).End(xlUp)(2).Resize(columnsize:=4) = varRicho
Click to expand...

if it could be that over 29 cells are empty but the output should not be
over 29 then change the code to:
With wksTarget
FERow = WorksheetFunction.Max(29, .Cells(49, 1).End(xlUp).Offset(1,
0).Row)
.Cells(FERow, 1).Resize(columnsize:=4) = varRicho
End With

Or look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbooks, download and open both and run the macro.


Regards
Claus B.
 
Hi Howard,



Am Thu, 16 Jan 2014 21:00:38 +0100 schrieb Claus Busch:






if it could be that over 29 cells are empty but the output should not be

over 29 then change the code to:

With wksTarget

FERow = WorksheetFunction.Max(29, .Cells(49, 1).End(xlUp).Offset(1,

0).Row)

.Cells(FERow, 1).Resize(columnsize:=4) = varRicho

End With



Or look here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for the workbooks, download and open both and run the macro.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

This did the trick and works well!

Thanks a lot.

I will look at the link, bound to be some good info there as usual.

wksTarget.Cells(49, 1).End(xlUp)(2).Resize(columnsize:=4) = varRicho

Sure do appreciate the help.

Howard
 
Hi Howard,



Am Thu, 16 Jan 2014 21:00:38 +0100 schrieb Claus Busch:






if it could be that over 29 cells are empty but the output should not be

over 29 then change the code to:

With wksTarget

FERow = WorksheetFunction.Max(29, .Cells(49, 1).End(xlUp).Offset(1,

0).Row)

.Cells(FERow, 1).Resize(columnsize:=4) = varRicho

End With



Or look here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for the workbooks, download and open both and run the macro.





Regards

Claus B.
Click to expand...


The download was very informative!

That is really nice to be able to have a target block of cells for the input and not have to put artificial "stops" at the top AND be able to skip over some occupied cells below the target cells.

Thanks Claus.
Howard
 
Back
Top