TTom
Well-known Member
- Joined
- Jan 19, 2005
- Messages
- 518
I've used the code (below) successfully in other workbooks, but now I am getting a message when code reaches:
.FormulaArray = ArrayPath
that opens a dialog box requesting I select the path and wb the array path is set to use.
The weird thing is I used the code in an existing program and my new program with the exact same array path, the old one works fine - no dialog box - the new program requires I path to the wb? I looked at both codes and I can not find difference. Any idea what I am missing, may disabling alerts (although I don't see that in the code that works w/o dialog?)
<code>
ArrayPath = "='" & FilePath & "[" & FileName & "]" & SheetN & "'!K31:O32" '2rx5c array
Cells(Range("A65000").End(xlUp).Row + 1, 1).Activate
rr = ActiveCell.Row
cc = ActiveCell.Column
Set DestRange = Sheets("Sheet4").Range(Cells(rr, cc), Cells(rr + 1, cc + 4)) '2rx5c array
Application.Goto DestRange
With DestRange
.FormulaArray = ArrayPath
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
</code>
.FormulaArray = ArrayPath
that opens a dialog box requesting I select the path and wb the array path is set to use.
The weird thing is I used the code in an existing program and my new program with the exact same array path, the old one works fine - no dialog box - the new program requires I path to the wb? I looked at both codes and I can not find difference. Any idea what I am missing, may disabling alerts (although I don't see that in the code that works w/o dialog?)
<code>
ArrayPath = "='" & FilePath & "[" & FileName & "]" & SheetN & "'!K31:O32" '2rx5c array
Cells(Range("A65000").End(xlUp).Row + 1, 1).Activate
rr = ActiveCell.Row
cc = ActiveCell.Column
Set DestRange = Sheets("Sheet4").Range(Cells(rr, cc), Cells(rr + 1, cc + 4)) '2rx5c array
Application.Goto DestRange
With DestRange
.FormulaArray = ArrayPath
.Copy
.PasteSpecial xlPasteValues
.Cells(1).Select
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
End With
</code>