accessing values in cells

[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

Darnit, I thought it would still work with the names of the worksheets being arbitrary, like Bob, Joe, Leslie, and so on. Can you change it to accomodate names, instead of just numbers? And, can you exclude the sheet called "Winners"

Thanks for the continual assistance.

[DRJ]

I'm not sure what the error is. The code compiles fine for me with no syntax errors.

To exclude the sheet Winners use

For i = 1 To 10
If Sheets("Sheet" & 1).Name <> "Winners" Then
With Sheets("Sheet" & i)
z.Cells(i, 2).Value = .Name
z.Cells(i, 3).Value = .Range("A2").Value
End With
Else
End If
Next i

[Tom Urtis]

Originally Posted by march madness

Darnit, I thought it would still work with the names of the worksheets being arbitrary, like Bob, Joe, Leslie, and so on. Can you change it to accomodate names, instead of just numbers? And, can you exclude the sheet called "Winners"

This will be my last post on this thread.

You've been asking these questions in small bits instead of just asking for what you want. This is getting messy because you are not specifying what should be done if "Winners" is among the first 10 sheets or not. The following two macros will cover either scenario there. But what if "Results" is among the first 10 sheets, do you want to include that too? And what if you don't have 11 sheets if "Winners" is among the 10 and you don't want to include it?

DRJ's code failed, although he said he could not see why, because:

(1) It relied on Sheet names, not indexes, the rules of which you changed after the fact.
(2) It placed the A2 value of each sheet in column B, not the sheet name like you asked.
(3) For some reason only he knows why, he coded his macro to place the word "Sheet" and then the A2 value divided by 10, in column C, instead of just the A2 value like you asked. That is why his code would show a bizarre sheet name - looking value like "Sheet1.4" in the wrong column if the number 14 were in cell A2 of one of those sheets.

You might also need to end up taking values off sheets based on their VBA object code names, instead of by Index number or sheet tab name.

At this point I do not know what you will end up needing, but here is a set of two macros that do what you want per your latest request, given two possible scenarios described above.

'First 10 sheets, by Index number, excluding "Winners"
Sub SheetArray_TakeFourA()
Dim i As Integer, z As Worksheet
Set z = Worksheets("Results")
For i = 1 To 10
With Sheets(i)
If .Name <> "Winners" Then
z.Cells(i, 2).Value = .Name
z.Cells(i, 3).Value = .Range("A2").Value
End If
End With
Next i
z.Range("B1:C10").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
End Sub

'First 10 sheets, by Index number, that are not "Winners"
Sub SheetArray_TakeFourB()
Dim i As Integer, z As Worksheet
Set z = Worksheets("Results")
z.Range("B1:C11").ClearContents
For i = 1 To 10
With Sheets(i)
If .Name <> "Winners" Then
z.Cells(i, 2).Value = .Name
z.Cells(i, 3).Value = .Range("A2").Value
End If
End With
Next i
z.Range("B1:C10").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
If Application.CountA(z.Range("B1:B10")) = 9 Then
With Sheets(11)
z.Cells(11, 2).Value = .Name
z.Cells(11, 3).Value = .Range("A2").Value
End With
z.Range("B1:C11").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
End If
End Sub

[march madness]

Originally Posted by Tom Urtis

This will be my last post on this thread.

You've been asking these questions in small bits instead of just asking for what you want. This is getting messy because you are not specifying what should be done if "Winners" is among the first 10 sheets or not. The following two macros will cover either scenario there. But what if "Results" is among the first 10 sheets, do you want to include that too? And what if you don't have 11 sheets if "Winners" is among the 10 and you don't want to include it?

DRJ's code failed, although he said he could not see why, because:

(1) It relied on Sheet names, not indexes, the rules of which you changed after the fact.
(2) It placed the A2 value of each sheet in column B, not the sheet name like you asked.
(3) For some reason only he knows why, he coded his macro to place the word "Sheet" and then the A2 value divided by 10, in column C, instead of just the A2 value like you asked. That is why his code would show a bizarre sheet name - looking value like "Sheet1.4" in the wrong column if the number 14 were in cell A2 of one of those sheets.

You might also need to end up taking values off sheets based on their VBA object code names, instead of by Index number or sheet tab name.

At this point I do not know what you will end up needing, but here is a set of two macros that do what you want per your latest request, given two possible scenarios described above.

'First 10 sheets, by Index number, excluding "Winners"
Sub SheetArray_TakeFourA()
Dim i As Integer, z As Worksheet
Set z = Worksheets("Results")
For i = 1 To 10
With Sheets(i)
If .Name <> "Winners" Then
z.Cells(i, 2).Value = .Name
z.Cells(i, 3).Value = .Range("A2").Value
End If
End With
Next i
z.Range("B1:C10").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
End Sub

'First 10 sheets, by Index number, that are not "Winners"
Sub SheetArray_TakeFourB()
Dim i As Integer, z As Worksheet
Set z = Worksheets("Results")
z.Range("B1:C11").ClearContents
For i = 1 To 10
With Sheets(i)
If .Name <> "Winners" Then
z.Cells(i, 2).Value = .Name
z.Cells(i, 3).Value = .Range("A2").Value
End If
End With
Next i
z.Range("B1:C10").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
If Application.CountA(z.Range("B1:B10")) = 9 Then
With Sheets(11)
z.Cells(11, 2).Value = .Name
z.Cells(11, 3).Value = .Range("A2").Value
End With
z.Range("B1:C11").Sort Key1:=z.Range("C1"), Order1:=xlAscending, Header:=xlNo
End If
End Sub

Thanks for the assistance and the patience. I asked in little bits because when I posted what I wanted, I didn't get what I wanted. I decided that either what I was asking for was too esoteric, too confusing, or some other reason that I didn't know. That made me decide to ask in steps. I thought that if I asked for generic solutions that would apply to my specific problem, and then build upon that, until I got the final generic solution to my specific problem, I'd have more success receiving possible solutions. Either it worked as I had predicted, or I just got lucky with you all seeing the post, who knows?
Again, thanks all for your help, I really appreciate it, and my apologies for any frustration I've caused.