printing of comments at end of sheet???

  • Thread starter Thread starter Tanya
  • Start date Start date
T

Tanya

Does anyone know why or more importantly how to fix that
when printing comments, they appear in order; ie B4, B6,
etc instead of B4, C4, B6, E6 at the end of the page?
thank you
 
You could use a macro to print the comments by column. The following
code inserts a new sheet, with a list of comments from Sheet1:
'===================================
Sub CommentTextNewSheet()
Dim cmt As Comment
Dim c As Range
Dim ws1 As Worksheet
Dim col As Integer
Dim str As String
Dim r As Long
r = 1
Set ws1 = Worksheets("Sheet1")
For col = 1 To ws1.UsedRange.Columns.Count
On Error Resume Next
If ws1.Columns(col).EntireColumn _
.SpecialCells(xlCellTypeComments) Is Nothing Then
Else
For Each c In ws1.Columns(col).EntireColumn _
.SpecialCells(xlCellTypeComments)
str = c.Address
Worksheets("Sheet2").Range("A" & r).Value _
= "Sheet1" & str
Worksheets("Sheet2").Range("B" & r).Value _
= Worksheets("Sheet1").Range(str).Comment.Text
r = r + 1
Next c
End If
Next col
End Sub
'==============================
 
Back
Top