This macro will work properly only if it is ran once per time sheet. If it
is run multiple times then you will have to let me know, because this code
will throw an error when it tries to create another worksheet named "Comments
Log". Also, I put the PrintOut method at the bottom of the code. Keep in
mind that it will use your current print settings. If you need to change
them each time, use this line of code instead, which will show the Print
Preview window and allow you to make changes.

' print sheet
wksComments.PrintOut Preview:=True

Hope this helps! If so, let me know, click "YES" below.

Sub ListComments()

Dim wksComments As Worksheet
Dim wks As Worksheet
Dim cmt As Comment
Dim InputRow As Long

' add new sheet at end of sheets
Set wksComments = Sheets.Add(After:=Sheets(Sheets.Count))
wksComments.Name = "Comments Log"

' find next available row
InputRow = wksComments.Cells(Rows.Count, "A").End(xlUp).Row + 1

' loop thru each sheet in workbook
For Each wks In Worksheets

' loop thru each comment in wks
For Each cmt In wks.Comments
With wksComments
' get sheet name of comment
.Cells(InputRow, "A").Value = wks.Name
' get cell address of comment
.Cells(InputRow, "B").Value = cmt.Parent.Address
' get comment text
.Cells(InputRow, "C").Value = cmt.Text
End With
InputRow = InputRow + 1
Next cmt

' don't allow comments to print on sheet
wks.PageSetup.PrintComments = xlPrintNoComments

Next wks

' print sheet
wksComments.PrintOut Copies:=1

End Sub
--
Cheers,
Ryan


"Shawn" wrote:

My organization uses a time sheet program built in excel. The auditor
requires a "comments box" be added to note any changes made to the time sheet
after the employee signs it.

Sometimes there ends up being dozens of comments boxes all over the time
sheet. We have to print each one and file them at the administrative office.

My questions are, is there a way the comments can be printed on a seperate
sheet? If so, can it list not only the content of the comments box but the
address of the cell it referenced? Then, can it be coded so the comments
boxes don't show up when the paper version is printed?


--
Thanks
Shawn