Hi, I am trying to assign an array that in stored in Sheet1 Range("A1:D102"), to the variable 'tarr' and then populate the same data "Sheet2" starting with cell "C2". But when I reach
"tarr = sh1.Range("A1", Range("A1").Offset(vc - 1, hc - 1))" it gives me the error "Method 'Range' of object '_Worksheet' failed
If I add "sh1.Select" before assigning the array variable then it works.
Same issue with "sh2.Range("C2", Range("C2").Offset(vc - 1, hc - 1)) = tarr".
If I add sh2.select then it works fine.
I have Worksheet_SelectionChange(ByVal Target As Range) active in Sheet 2 and this cannot be disabled. It fires every time the sheet gets selected.
Is there a way around this?
"tarr = sh1.Range("A1", Range("A1").Offset(vc - 1, hc - 1))" it gives me the error "Method 'Range' of object '_Worksheet' failed
If I add "sh1.Select" before assigning the array variable then it works.
Same issue with "sh2.Range("C2", Range("C2").Offset(vc - 1, hc - 1)) = tarr".
If I add sh2.select then it works fine.
I have Worksheet_SelectionChange(ByVal Target As Range) active in Sheet 2 and this cannot be disabled. It fires every time the sheet gets selected.
Is there a way around this?
VBA Code:
Public tarr() As Variant, sh1 As Worksheet, sh2 As Worksheet
Sub verifyarr()
Dim vc As Long, hc As Long
Set sh1 = ThisWorkbook.Worksheets("Sheet1")
Set sh2 = ThisWorkbook.Worksheets("Sheet2")
vc = sh1.Range("A" & Rows.Count).End(xlUp).Row
hc = sh1.Cells(1, Cells.Columns.Count).End(xlToLeft).Column
sh1.Select
tarr = sh1.Range("A1", Range("A1").Offset(vc - 1, hc - 1))
sh2.Range("C2", Range("C2").Offset(vc - 1, hc - 1)) = tarr
End Sub