Write values based on a array to a certain cell across worksheets Excel VBA

September 12, 2023

I am trying to assign a list of names to a certain cell (E4) across all worksheets. This is what I have so far. I am not sure the proper way of assigning values from a array across all worksheets. The error message says the subscription is out of range. But I can’t find a solution to fix it. Thank you

Sub test()

Dim ws1 As Worksheet
Dim i As as Long
Dim myArray As Variant
Set ws1 = Worksheets("Sheet9")
myArray = Worksheets("data").Range("range")
 With ws
    For Each ws In ActiveWorkbook.Worksheets
        ws.Range(i, 4) = myArray(i)
    Next
  
 End With


End Sub

>Solution :

myArray is a one-based, 2D array.

Sub test()
    Dim myArray As Variant
    myArray = ActiveWorkbook.Worksheets("data").Range("range")

    Dim i As Long
    For i = LBound(myArray, 1) to UBound(myArray, 1)
         ActiveWorkbook.Worksheets(i).Range("E4").Value = myArray(i, 1)
    Next
End Sub