Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Excel 2013 VBA 'Range on another worksheet' question

I'm trying to dump the data in an array in a range of cells on the sheet that's not the ActiveSheet.

 
  Sheets("Sheet2").Range(Cells(5, 4), Cells(36, 13)).Value = arrMIDIEvents

Open in new window


I get an 'Application-defined or Object-defined error'.

However, it works if I Activate the other sheet and dump it. But I'd like to avoid leaving the ActiveSheet. Isn't that possible?

Thanks
0
hindersaliva
Asked:
hindersaliva
  • 2
2 Solutions
 
hindersalivaAuthor Commented:
Nope. Sorry Simon. It made no difference.
0
 
SimonCommented:
Sorry, I deleted my suggestion before you responded after testing it.

This does work though:
Sub writeToNonActiveSheet()
arrMIDIEvents = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'Sample array to test with
Range(Sheets(2).Cells(5, 4), Sheets(2).Cells(36, 13)).Value = arrMIDIEvents
End Sub

Open in new window


I'd probably use an object variable to refer to the destination worksheet
Sub writeToNonActiveSheet2()
Dim destSheet As Worksheet
Set destSheet = Sheets(2)
arrMIDIEvents = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 'Array to test with
destSheet.Range(destSheet.Cells(5, 4), destSheet.Cells(36, 13)).Value = arrMIDIEvents
End Sub

Open in new window

0
 
Hakan YılmazTechnical Office MEP EngineerCommented:
You may use With keyword to shorten your references.
With ThisWorkbook.Worksheets("yoursheetname")
    .Range(.Cells(5, 4), .Cells(36, 13)).Value = arrMIDIEvents
End With

Open in new window


And keep in mind, ThisWorkbook.Sheets is not the same as ThisWorkbook.Worksheets. Because some sheets doesn't have Range and Cells properties.
0
 
hindersalivaAuthor Commented:
Perfect! Thank you all.
What I missed was, the .Cells(r, c) needs to be fully addressed with the Sheet name. I put it in the wrong place.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now