locked
copy comments alongwith cells data RRS feed

  • Question

  • Hi all,

    I'm trying to copy comments along with cell's value to another worksheet but I'm facing problems.

    The comment copies easily but the original comments formatting are missing from the new worksheet.

    Here's my code:-

    If Not Cell.Comment.Text = "" Then
                        With newWorksheet.Cells(Cell2.Row, col)
                        .AddComment Cell.Comment.Text
                        .Comment.Shape.TextFrame.Characters.Font.ColorIndex = Cell.Comment.Shape.TextFrame.Characters.Font.ColorIndex
                        .Comment.Shape.TextFrame.Characters.Font.Size = Cell.Comment.Shape.TextFrame.Characters.Font.Size
                        .Comment.Shape.TextFrame.Characters.Font.Name = Cell.Comment.Shape.TextFrame.Characters.Font.Name
                        End With
                        End If

    My comment includes some text in bold and other in normal font.

    I want that the original comment with all it's formatting gets copied easily.

    Can anyone help??

    Thanks in advance.

    Ashish Mathur

    Thursday, January 31, 2013 9:57 AM

All replies

  • Hi Ashish,

    Wouldn't it be easier to copy/paste the comment, using

    Cell.Copy
    newWorksheet.Cells(Cell2.Row, col).PasteSpecial Paste:=xlPasteComments


    Regards, Hans Vogelaar

    Thursday, January 31, 2013 10:21 AM
  • Hi Hans,

    Thanks for your reply. I tried this code but it's very time consuming as I've lots of rows and many sheets where I've to run the same macro. Hence, it would be better if some less time consuming code is available.

    Please provide some hints to the problem.

    Thanks

    Ashish Mathur

    Thursday, January 31, 2013 10:41 AM
  • Have you tried setting

    Application.ScreenUpdating = False

    at the beginning of your code, and

    Application.ScreenUpdating = True

    at the end? That usually speeds up execution significantly.

    Parsing the text of the comment for all possible formatting would probably be more time-consuming than pasting the comment in its entirety.


    Regards, Hans Vogelaar

    Thursday, January 31, 2013 10:55 AM
  • Apart from what Mr Hans Suggested,without looping individual cells you can copy the entire or a part of cells with comments.

    Following lines will copy entire comment and values

    Worksheets(1).UsedRange.Copy
    Worksheets(2).Range(Worksheets(1).UsedRange.Cells(1).Address).PasteSpecial _
        Paste:=xlValues
    Worksheets(2).Range(Worksheets(1).UsedRange.Cells(1).Address).PasteSpecial _
        Paste:=xlComments
    
    But you have to consider how big block you can copy in one shot.



    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, January 31, 2013 11:26 AM
    Answerer
  • Hi Hans,

    Thanks for your reply. I tried the setting Application.ScreenUpdating = False and Application.ScreenUpdating = True but the macro is still running slow.

    In my excel application, i've around 50 sheets, each with around 400 rows. The macro for comments will run on each sheets copying 400 rows and a number of columns  (from K to BW). Thus, i would require an optimum solution for this problem.

    Please provide some hints to the problem.

    Regards

    Ashish Mathur

    Friday, February 1, 2013 3:10 AM

  • Hi Asadulla,

    Thanks for your reply. I tried the given suggestion but the macro is still running slow.

    In my excel application, i've around 50 sheets, each with around 400 rows. The macro for comments will run on each sheets copying 400 rows and a number of columns  (from K to BW). Thus, i would require an optimum solution for this problem.

    Please provide some hints to the problem.

    Regards

    Ashish Mathur

    Friday, February 1, 2013 3:56 AM
  • If Asadulla's suggestion doesn't help, I fear that the slowness of the macro is due to the large amount of data to be copied.


    Regards, Hans Vogelaar

    Friday, February 1, 2013 8:18 AM
  • Yes Mr Hans.

    @Ashis:

    The main cause of delay is due to excessive looping.Probably in your scenario you can avoid rowwise and columnwise looping.

    Only thing of concern is how large block you can copy in one sheet.I assumed entire usedrange of sheet.It will not cost much time.

    But you may need to loop for each sheet.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, February 1, 2013 8:26 AM
    Answerer
  • Try to run this procedurę before and after job:

    'before
    call BlockEvScreenCalc(False, "I'm doing something...")
    'after
    call BlockEvScreenCalc(true)
    
    
    Public Sub BlockEvScreenCalc(Optional ByVal bWlacz As Boolean = True, _
                                 Optional Status As String = "")
        On Error Resume Next
        With Application
            If bWlacz Then
                .EnableEvents = True
                .Calculation = xlCalculationAutomatic
                .ScreenUpdating = True
                .Cursor = xlDefault
            Else
                .ScreenUpdating = False
                .Calculation = xlCalculationManual
                .EnableEvents = False
                .Cursor = xlWait
            End If
            .StatusBar = Status
        End With
    End Sub


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Friday, February 1, 2013 11:05 AM
    Answerer