I'm not quite sure, but maybe...

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim rptWks As Worksheet

Dim oRow As Long

Dim DateRow As Long
Dim CommentRow As Long

Dim myDate As Date

Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long

myDate = Application.InputBox(prompt:="enter date:", Type:=1)

If IsDate(myDate) Then
'keep going
Else
MsgBox "Please try again!"
Exit Sub
End If

If Year(myDate) < 2005 _
Or Year(myDate) 2010 Then
MsgBox "Hey, that date: " & Format(myDate, "mmmm dd, yyyy") _
& " doesn't look right!"
Exit Sub
End If

DateRow = 4
CommentRow = 29
FirstCol = 1

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("report").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Set rptWks = Worksheets.Add
rptWks.Name = "Report"
With rptWks.Range("a1").Resize(1, 4)
.Value = Array("Date", "Worksheet" & Chr(10) & "Name", _
"Address", "Comment")
.WrapText = True
End With

oRow = 1
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = rptWks.Name Then
'do nothing
Else
With wks
LastCol = .Cells(DateRow, .Columns.Count).End(xlToLeft).Column
For iCol = FirstCol To LastCol
If .Cells(DateRow, iCol).Value2 = CLng(myDate) Then
'found a match
oRow = oRow + 1
With rptWks.Cells(oRow, 1)
.Value = myDate
.NumberFormat = "mm/dd/yyyy"
End With
rptWks.Cells(oRow, 2).Value = "'" & .Name
rptWks.Cells(oRow, 3).Value _
= .Cells(DateRow, iCol).Address(0, 0)
rptWks.Cells(oRow, 4).Value _
= .Cells(CommentRow, iCol).Value
End If
Next iCol
End With
End If
Next wks

With rptWks.UsedRange
With .Columns
.ColumnWidth = 255
.AutoFit
End With
With .Rows
.AutoFit
End With
End With
End Sub

And this deletes the Report worksheet before it starts do the real work--you
won't have to delete it (manually or via a different button).


Kenshe wrote:

It does look to be difficult this way.
I know there must be a simplier way to do this, I am not sure though
how to do this.

The date is manually entered in each column and is always entered in
Row 4 regardless of column, using a cell comment instead of tool bar
comments, those will always be in Row 29 regardless of column also.
Here's how it's designed:

By using a button to create a new sheet.
Go out and search for data in row 29 on a specified date in all sheets
(29 of them).
Generate report in new sheet created.
Reporting: Date (listed in Row 4), Address (B4, F4..ect) and whatever
data is in Row 29 cell (comments)
Using another button to delete report sheet created when finished with
it.
(both buttons will be on another sheet labeled "Report")

So the macro need to search every sheet for a specified date, capture
any data listed in row4 and row29 and its address (B4) on the specified
date only and place all data on a new sheet. Simple??

I am learning, without the extensive knowledge it becomes difficult,
however getting as far as I have with understanding how it works
(somewhat) keep me learning!

If possible, a solution would help alot!

Thanks again!

--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

--

Dave Peterson