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

pirdop

Board Regular
Joined
Jul 27, 2010
Messages
72
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!

Code:
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Board Regular
Joined
Jul 27, 2010
Messages
72
Thanks it works perfectly, but would you specify what's wrong with my code and why it is better to use WITH statement?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,814
Messages
5,655,454
Members
418,201
Latest member
BaconMcSandwich

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top