vba: getting value of a cell across all tabs

Question

Greetings,

I'm trying to write a vbscript function that collects the value of a cell ("E1") across all tabs of an excel document into an array (Reviewers).

I wrote the following code, but the data is not being stored into the array, can someone please tell me what I'm doing wrong?

this is the code: http://img24.imageshack.us/my.php?image=image001dx2.jpg

Answer 1

I can't see your code as my company has blocked access to imageshack.

Have you tried something like the following?

Dim i As Long
Dim reviewers() As String
ReDim reviewers(0 To Worksheets.Count - 1)

For i = 0 To Worksheets.Count - 1
    reviewers(i) = Worksheets(i + 1).Cells(1, 5).Value
Next

Answer 2

Or even, since Worksheets is 1-based (so we can lose that +1 and -1 business):

Dim i As Long
Dim reviewers() As String
ReDim reviewers(1 To Worksheets.Count)

For i = 1 To Worksheets.Count 
    reviewers(i) = Worksheets(i).Cells(1, 5).Value
Next

Answer 3

Your code says

 Reviewers(N) = Sheets(N+1).Range("B7:C7").Value

Range B7:C7 consists of two cells, not one, and therefore the value is an array, not a single value. Use a range of one cell.