vba import cells from other workbooks error

Question

I am trying to copy some range of cells from other workbooks, but I get the error:

'runtime '1004' error 
Error defined by application or object

if I try to use the "range(cells(i,j), cells(k,h))" sintax instead of the range("A1:Z1"). I.e., In the following code the line "PASTE 1" produces an error, while the line "PASTE 2" runs smoothly (obviously I don't want to use the second one because I need to run a loop over different ranges).

Sub Importa()

Dim directory As String
Dim fileName  As String
Dim wbfrom    As Workbook
Dim wbto    As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "mydirectory"
fileName = Dir(directory & "*.xl??")   'find the first *.xl?? file; '  wildcards: multiple character (*) single character (?)
Set wbto = ThisWorkbook
Set wbfrom = Workbooks.Open(directory & fileName, False, True)

' copy some cells
wbfrom.Sheets(1).Range(Cells(9, 6), Cells(15, 6)).Copy

'PASTE 1
wbto.Sheets(1).Range(Cells(9, 1), Cells(15, 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'PASTE 2
'wbto.Sheets(1).Range("A1:A8").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


wbfrom.Close SaveChanges:=False
'Turn on screen updating and displaying alerts again
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Welcome, this question is very similar to one asked just today. Look [here](http://stackoverflow.com/questions/33041178/vba-for-each-worksheet-in-workbook) — Scott Craner, Oct 09 '15 at 18:32

score 1 · Accepted Answer · answered Oct 09 '15 at 18:40

the problem is that you need to qualify the Cells to a particular sheet/workbook. Otherwise, it is implictly belonging to the ActiveSheet, and since the wbFrom is Active at run-time, the range cannot exist (because cells on one worksheet cannot define a range on another worksheet)

Two ways to handle this, one is qualifying Cells like so:

With wbto.Sheets(1)
    .Range(.Cells(9, 1), .Cells(15, 1)).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

The other is to coerce the address from the cells:

wbto.Sheets(1).Range(Cells(9, 1).Address, Cells(15, 1).Address).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

My preference is for the first option, as it tends to be more dynamic and easy to read and modify later, if you need to do so.

vba import cells from other workbooks error

1 Answers1