If you select the Ratings worksheet and hit Edit|goto|special, you can select
just the cells with comments.

You can use that feature in your code. So you don't have to look at all the
cells without comments--just the ones that have them!


Option Explicit
Sub Fetch_comment2()

Dim ComWks As Worksheet
Dim RateWks As Worksheet
Dim ComRng As Range
Dim myCell As Range

Set RateWks = Worksheets("ratings")

With RateWks
On Error Resume Next
Set ComRng = .Range("b3", .Cells.SpecialCells(xlCellTypeLastCell)) _
.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If ComRng Is Nothing Then
'no comments
Exit Sub
End If

Sheets.Add Befo=Worksheets(Worksheets.Count), Type:=xlWorksheet
Set ComWks = ActiveSheet
ComWks.Name = "Comments"

.Range("1:2").Copy _
Destination:=ComWks.Range("A1")
.Range("A:A").Copy _
Destination:=ComWks.Range("A1")

For Each myCell In ComRng.Cells
ComWks.Range(myCell.Address).Value _
= myCell.Comment.Text
Next myCell
End With

End Sub

ps. When you did this:

Dim x, y As Integer

You actually declared Y as an integer, but X was declared as a Variant.

Dim x as long, y as long

(I like long's!)

would be one way to fix this.

Shilps wrote:

Hi,

I have this piece of code. The objective is to make a copy of 'Ratings'
sheet and transfer all the comments in that sheet as cell values in '
Comments' worksheet

----------------------------------------------------------
Option Explicit

Sub Fetch_comment()
Dim x, y As Integer
Sheets.Add Befo=Worksheets(Worksheets.Count), Type:=xlWorksheet
ActiveSheet.Name = "Comments"
Worksheets("Ratings").Range("1:2").Copy Destination:= _
Worksheets("Comments").Range("A1")
Worksheets("Ratings").Range("A:A").Copy Destination:= _
Worksheets("Comments").Range("A1")
For x = 3 To 100
For y = 2 To 156
Worksheets("Comments").Cells(y, x).Value = _
Worksheets("Ratings").Cells(y, x).Comment.Text
Next
Next
End Sub

-------------------------------------------------------
There are 2 problems

1)After creating the new sheet, if I use this code
Worksheets(Worksheets(Worksheets.Count)).Name = "Comments"
for naming the newly created sheet, it gives Error 13- Type mistmatch. Why
is it giving that error?
2) It is giving Run Time Error 91- Object Variable or With Block Variable
not set at line
Worksheets("Comments").Cells(y, x).Value = _
Worksheets("Ratings").Cells(y, x).Comment.Text

Please tell me what is the error in this code.
TIA
Shilps

--

Dave Peterson