Dave,
Thanks for the macro, it really works great! The other macro from your
suggested site is a little more problematic for what I am trying to
achieve. I have been working with the one you posted and I am having a
little trouble in modifying it.
It works great and is ready to use, but I notice I needed to generate
the "comment" report by date. I wanted to generate a new report every
day leaving out the previous comments already generated the day before.
Another words, the current comments for that day are the only coments
being reported. For once I create a report and print it, I delete the
created report page (sheet 1) for that day. I dont want to keep
reporting the old comments along with the new.
Here is the modified macro I am using:
Private Sub CommandButton1_Click()
Dim cmt As Comment
Dim wks As Worksheet
Dim rptWks As Worksheet
Dim DestCell As Range
ActiveWorkbook.Unprotect
Set rptWks = Worksheets.Add
With rptWks
..Range("a1").Resize(1, 3).Value _
= Array("Sheet", "Location", "Comment")
Set DestCell = rptWks.Range("a2")
End With
With ActiveSheet.Range("C1")
..ColumnWidth = 600 / .Width * .ColumnWidth
End With
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = rptWks.Name Then
'do nothing
Else
For Each cmt In wks.Comments
DestCell.Value = "'" & wks.Name
DestCell.Offset(0, 1).Value = cmt.Parent.Address(0, 0)
DestCell.Offset(0, 2).Value = cmt.Text
Set DestCell = DestCell.Offset(1, 0)
Next cmt
End If
Next wks
Sheets("Comment Rpt").Select
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
Private Sub CommandButton2_Click()
'
' SheetDelete Macro
' Macro recorded 12/9/2005 by Oliver User
'
'
Sheets("Sheet1").Select
ActiveWorkbook.Unprotect
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Protect Structure:=True, Windows:=False
End Sub
Maybe it would be better not use the "comment" window and just enter
any needed comments in a cell and use a bunch of "if then" statements
to do what I need with more avenues of attack??
Thanks again for all your help!
Ken