accessing values in cells

[march madness]

If I want to access the values in cells when the cells are in different worksheets, how do I do that? i.e., there are 10 worksheets, 10 cells and 10 values in those cells. I want to create an array of those values. How do I do it? Note: I will be creating a new array of the same cells' values often, and the values will be changing. Each time I create the array, the array with the new values will overwrite the old array, and it's old values, so I'd like to automate this as much as possible.

For the sake of the example, let's say the values are in cell A2 in each worksheet, and each worksheet has it's default name, i.e. "Sheet1."

Any suggestions?

[HalfAce]

Not sure if you still need it or not, but assuming you want to show the values of A2 from all 10 sheets in A1 of sheet 1....

Sub Test()
[A1] = Sheets("Sheet1").[A2] & ", " & Sheets("Sheet2").[A2] & ", " & Sheets("Sheet3").[A2] & ", " & Sheets("Sheet4").[A2] & ", " & Sheets("Sheet5").[A2] & ", " _
& Sheets("Sheet6").[A2] & ", " & Sheets("Sheet7").[A2] & ", " & Sheets("Sheet8").[A2] & ", " & Sheets("Sheet9").[A2] & ", " & Sheets("Sheet10").[A2]
End Sub

Is this what you are (were?) looking for?
Dan

[Tom Urtis]

Something like this would update and place those 10 values in range B1:B10 of the active sheet.

Sub SheetArray()
Dim i As Integer
For i = 1 To 10
Cells(i, 2).Value = Sheets("Sheet" & i).Range("A2").Value
Next i
End Sub

[march madness]

Originally Posted by Tom Urtis

Something like this would update and place those 10 values in range B1:B10 of the active sheet.

Sub SheetArray()
Dim i As Integer
For i = 1 To 10
Cells(i, 2).Value = Sheets("Sheet" & i).Range("A2").Value
Next i
End Sub

That's what I asked for, thanks.

Is there any chance you know how I can associate those values with the name of the sheet the values originate from? Say the values are 10,20,30,40,50,60,70,80,90,100, and the sheet names are: Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6,Sheet7,Sheet8,Sheet9,Sheet10, respectively.
What I'd like to do is have in column B the names of the sheets, and in column C the associative values. How might I do that?

[DRJ]

Like this?

Sub SheetArray()
Dim i As Integer
For i = 1 To 10
Cells(i, 2).Value = Sheets("Sheet" & i).Range("A2").Value
Cells(i, 3).Value = "Sheet" & Sheets("Sheet" & i).Range("A2").Value / 10
Next i
End Sub

[Tom Urtis]

Originally Posted by march madness

Is there any chance you know how I can associate those values with the name of the sheet the values originate from? What I'd like to do is have in column B the names of the sheets, and in column C the associative values.

The below macro would accomplish that for you:

Sub SheetArray_TakeTwo()
Dim i As Integer
For i = 1 To 10
With Sheets("Sheet" & i)
Cells(i, 2).Value = .Name
Cells(i, 3).Value = .Range("A2").Value
End With
Next i
End Sub

[march madness]

Originally Posted by Tom Urtis

The below macro would accomplish that for you:

Sub SheetArray_TakeTwo()
Dim i As Integer
For i = 1 To 10
With Sheets("Sheet" & i)
Cells(i, 2).Value = .Name
Cells(i, 3).Value = .Range("A2").Value
End With
Next i
End Sub

Thanks much.
Two last things, if you don't mind:
1)How might I include in that same macro an ascending sort for both columns, so that if Sheet5's value is 40 and Sheet4's value is 50, Sheet4 will come first?
2)Make this macro work in another worksheet, say the sheet called, Results?

Thanks much.

[Tom Urtis]

Sub SheetArray_TakeThree()
Dim i As Integer, z As Worksheet
Set z = Worksheets("Results")
For i = 1 To 10
With Sheets("Sheet" & i)
z.Cells(i, 2).Value = .Name
z.Cells(i, 3).Value = .Range("A2").Value
End With
Next i
z.Range("B1:C10").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
End Sub

[march madness]

Originally Posted by Tom Urtis

Sub SheetArray_TakeThree()
Dim i As Integer, z As Worksheet
Set z = Worksheets("Results")
For i = 1 To 10
With Sheets("Sheet" & i)
z.Cells(i, 2).Value = .Name
z.Cells(i, 3).Value = .Range("A2").Value
End With
Next i
z.Range("B1:C10").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
End Sub

That's what I'm talking about! Thanks.

[march madness]

Originally Posted by DRJ

Like this?

Sub SheetArray()
Dim i As Integer
For i = 1 To 10
Cells(i, 2).Value = Sheets("Sheet" & i).Range("A2").Value
Cells(i, 3).Value = "Sheet" & Sheets("Sheet" & i).Range("A2").Value / 10
Next i
End Sub

Not that I was trying to ignore your help, this one just reported a syntax error, and Tom came back with one that worked. Thanks for the input though. BTW, what's the syntax error here?