PDA

View Full Version : Solved: Help with cell display, sheets loop



bturner2
09-02-2004, 10:27 AM
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
09-02-2004, 11:15 AM
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
09-02-2004, 03:07 PM
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
09-02-2004, 03:31 PM
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
09-04-2004, 01:31 PM
Ok, thanks for the help. Your solution worked like a charm.

Zack Barresse
09-04-2004, 04:02 PM
Your welcome. Glad it worked for ya. :)

Take care!