VBA Runtime error 91 when already used SET?

Question

I'm getting

Runtime Error '91': Object variable or with block variable not set

when I already used the set keyword. I read this old post that says set is used to assign a reference to an object. What does it really meaning?

Also read about POST A & POST B but no much help.

Dim strPath2 As String
Dim wbkWorkbook1 As Workbook
Dim wbkWorkbook2 As Workbook
Dim wbkWorksheet1 As Worksheet
Dim wbkworksheet2 As Worksheet

Dim rng As Range
Dim copyArr As Range
Dim lastcol As Integer

'define paths and filenames
strPath = "C:\Users\Account\Desktop\test.csv"

'open files
Set wbkWorkbook1 = ThisWorkbook
Set wbkWorkbook2 = Workbooks.Open(strPath)
Set wbkWorksheet1 = wbkWorkbook1.Worksheets("paste")
Set wbkworksheet2 = wbkWorkbook2.Worksheets("copy")

Dim i As Long

'Get all columns
With wbkworksheet2
    lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

Set copyArr(i) = wbkworksheet2.Cells(1, lastcol) ' Error

`copyArr` is `Nothing`. Even if it was not, you would not be able to assign its cells with `Set`. If you meant `copyArr` to be an array of `Range`s, declare it as an array and redim it to have dimensions. If you meant `copyArr` to be a range and assign values to individual cells of that range, assign it to a range first and then do not use `Set` when assigning cell values. — GSerg, Jan 10 '18 at 08:50

score 0 · Accepted Answer · answered Jan 10 '18 at 10:52

Set assigns an object reference to a variable.

wbworksheet2.Cells(1, lastcol)

returns an object Range, not an array.

Additionally, by your worksheets names, I am guessing you may want to copy and paste the values, so you can replace the last line with something like

   'clear any thing on clipboard to maximize available memory 
   Application.CutCopyMode = False 

   'copy the range from source book 
   wbkworksheet2.Cells(1, lastcol).Copy 

   'paste the data on the target book 
   wbkworksheet1.Range("A1").PasteSpecial

VBA Runtime error 91 when already used SET?

1 Answers1