Copy->Paste special Macro not working as expected!?

[pirdop]

Hi All,
to start with I'm new to VBA and all the procedures that exist but need to build a working code for a bigger project.
Here is the project in brief:
I need to clean all sheets in a workbook from any formulas and comments,
copy/move certain sheets to new workbooks and then send the new workbooks to a predefined list of people(email addresses).

Currently I'm working on the first part And the problem I encountered:
the following code is working fine, but it doesn't remove the comments!

Sub Convert_To_Values_Only()
Dim i As Long

MsgBox (ThisWorkbook.Sheets.Count)
For i = 1 To ThisWorkbook.Sheets.Count
    Sheets(i).Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,    SkipBlanks _:=False, Transpose:=False
        
Next i

End Sub

However when I repeat the procedure manually (with the mouse) the comments are gone.

Does anyone know what's wrong with the code?

And please don't help me yet with the other parts of the projects. I have to try them first myself and will ask you later.

Have to learn the VBA

btw would you recommend a book with all functions/procedures properties used in excel VBA

thanks in advance

 

[VoG]

Try

Sub Convert_To_Values_Only()
Dim i As Long
MsgBox (ThisWorkbook.Sheets.Count)
For i = 1 To ThisWorkbook.Sheets.Count
    With Sheets(i)
        With .UsedRange
            .Value = .Value
            .ClearComments
        End With
    End With
Next i
End Sub

 

[pirdop]

Thanks it works perfectly, but would you specify what's wrong with my code and why it is better to use WITH statement?