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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,006
Messages
5,508,750
Members
408,692
Latest member
OptimalKR

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top