Is there a way to copy all the comments in one workbook to another workbook?

Question

Hi I am trying to copy all the comments in a worksheet("HR&Finance") in Workbook1 to another worksheet("Budget") in Workbook2.

So far I recorded the below code in the worksheet in workbook1 and ran the code with the two workbooks open but it says subscript out of range error...

Can you give me some advice on how to fix this?

Thank you!

 Sub comment()
'
' comment Macro
'

'
    Range(A:Z).Select
    Selection.Copy
    Windows("Workbook2").Activate
    Range(A:Z).Select
    ActiveSheet.Paste
End Sub

Don't use `Windows("Workbook2").Activate` - see [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for how to properly reference workbooks and worksheets. — BigBen, Nov 20 '19 at 14:04
Record using Home > Find & Select > Go To Special... and select Notes from the selection. Then Copy and paste. Not sure if this will really work.... Excel help says Notes is equivalent to Comments. I just tried this. It copies the entire cell contents and the not just the note. Apparently, Notes are what have always been comments. Comments are now some way of attaching commentary from multiple people to a cell. — Tim Nevins, Nov 20 '19 at 14:16
I'm still not sure if you are talking about comments in a cell, or notations in a cell — Davesexcel, Nov 20 '19 at 14:20

score 2 · Answer 1 · answered Nov 20 '19 at 14:27

Sub CopyComments()
Dim W As Workbook, W1 As Workbook, Sh As Worksheet, Sh1 As Worksheet
    Set W = Workbooks("Workbook1.xlsx")
    Set W1 = Workbooks("Workbook2.xlsx")
      Set Sh = W.Worksheets("HR&Finance")
      Set Sh1 = W1.Worksheets("Budget")

      Sh.UsedRange.Copy

      W1.Activate: Sh1.Activate
      Sh1.Range("A1").PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
                                        SkipBlanks:=False, Transpose:=False
      Application.CutCopyMode = False
End Sub

But, the structure of the page where the comments are pasted special must be the same with the one where from they are copied...

Yes, it is correct. I just wanted to bring him in the sheet keeping the result. — FaneDuru, Nov 20 '19 at 15:01
@FaneDuru your code works perfectly too! Thank you both of you :) Geniuses in excel! — MooChang Park, Nov 20 '19 at 15:04

score 1 · Accepted Answer · answered Nov 20 '19 at 14:38

1

I have used the same area as your code, and pasted the comments to the book2 cells. The comments will appear in the same place as the 1st workbook.

If you wanted a list of comments, then we would have to do something else

Assuming the code will be in the first workbook.

Sub CopyComments()
    Dim bk1 As Workbook, bk2 As Workbook
    Dim sh1 As Worksheet, sh2 As Worksheet

    Set bk1 = ThisWorkbook
    Set bk2 = Workbooks("Book2.xlsx") ' change name of workbook
    Set sh1 = bk1.Sheets("HR&Finance")
    Set sh2 = bk2.Sheets("Budget")

    With sh1
        .Range("A:Z").SpecialCells(xlCellTypeConstants, 23).Copy
        sh2.Range("A1").PasteSpecial xlPasteComments
    End With

    Application.CutCopyMode = False

End Sub

answered Nov 20 '19 at 14:38

Davesexcel

6,896
2
27
42

Hi! thank you for the help. I copied the code and pasted in the vba of "HR&Finance" worksheet in Workbook1 and ran the code but it says subscript out of range :( – MooChang Park Nov 20 '19 at 14:52
1

You have to make sure the sheet names are correct and the workbook names, the code should also be in a regular module, not a worksheet module – Davesexcel Nov 20 '19 at 14:57
super genius! thank you it works ! Learned a lot today from you:) – MooChang Park Nov 20 '19 at 15:01

Is there a way to copy all the comments in one workbook to another workbook?

2 Answers2