Pages

Excel VBA Offset cannot SET Range over 65536 in .xlsx

Today I spent an hour and a half trying to figure out why the following code wouldn't work:

for i = 1 to rowcount
     myCell.value = sourceCell.value
     set myCell = myCell.Offset(1,0)
next

The highlighted section is what my debugger was showing as an error. In this example, my sourceCell was in a separate workbook from myCell and my code would not set myCell to A65537. It turns out that in order for your code to select anything over 65536 in an .xlsx file you must have the workbook that you are writing to activated. Like I said, it took me an hour and a half to find a forum online that mentioned this at all.

For anyone else that has this issue, I hope this helps.

mySheet.activate