Help with cell display, sheets loop

[bturner2]

Here is what I am trying to do. I have a workbook with 10 sheets in it. In cell A1 I have a name. Each sheets has a different name. Such as Bill, Ben, Bob....

What I need is for my script to look at cell A2 of each sheet,and see if the number in the cell is greater than 1. If it is greater than 1 I need it to copy the name that is in cell A1 and remmber it. When it has checked all the sheets I need it to display a mesage listing all of the names of the sheets that cell A2 was greater than 1. I also need to be able to have a button on the message that is displayed that will allow me to print the list.

Anyone want to give this a shot? I just dont have enough hours in the day to figure it out. Thanks.

[Zack Barresse]

Sure,

Something like this...

Option Explicit
Sub forbTurner2()
    Application.DisplayAlerts = False
    Dim ws As Worksheet, tmpMsg As String, tmpSht As Worksheet, tmpName As String
    Set tmpSht = Worksheets.Add
    tmpName = ActiveSheet.Name
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        If ws.Name <> tmpName Then
            With ws
                If .Range("A2").Value > 1 Then
                    tmpSht.Range("A65536").End(xlUp).Offset(1).Value = .Range("A1").Value
                    tmpMsg = tmpMsg & Range("A1").Value & vbCr
                End If
            End With
        End If
    Next ws
    If tmpMsg <> "" Then
        If MsgBox("Your list of values are as follows:" & vbCr & vbCr & tmpMsg & vbCr & _
            "Do you wish to print this list?", vbYesNo + vbQuestion, "Complete") = vbYes Then GoTo myYes
        tmpSht.Delete
    End If
    GoTo myEnd
myYes:
    tmpSht.PrintOut copies:=1
    tmpSht.Delete
myEnd:
    Application.DisplayAlerts = False
End Sub

If you wanted to add a title line or something tothe print out sheet, that wouldn't be a problem.

[bturner2]

Actualy I think I would like to add a title to the temp sheet. And I can tell it is going to take me a little while to look at what you did to see exactly what you are doing. I just hope Ic an good enough at this stuff to start helping other people.

[Zack Barresse]

Sure,

Right before the "For Each ws In ..." line, put something like this ...

With tmpSht.Range("A1")
        .Value = "Your Title Here"
        .Font.Bold = True 'Want bold?
        .Font.Italic = True 'Want Italics too?
    End With

Let me know how it works.

[bturner2]

Ok, thanks for the help. Your solution worked like a charm.

[Zack Barresse]

Your welcome. Glad it worked for ya.

Take care!