Copy Comments Automatically using Excel VBA

Опубликовано: 14 Январь 2025
на канале: Dinesh Kumar Takyar

16,918

Copying and pasting 500 comments manually can be really a tough boring task. That's what Iqbal is doing. We can free a lot of time for Iqbal if we can automate the process of copying and pasting the comments using Excel VBA.
In the process we learn about ScreenUpdating, Error handing, IF function, Special Cells and for loops!
Here's the complete VBA code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim commentsrng As Range
Dim copycell As Range
Dim comments As Worksheet

Set comments = ActiveSheet

On Error Resume Next

Set commentsrng = comments.Cells.SpecialCells(xlCellTypeComments)
On errror GoTo 0

If commentsrng Is Nothing Then
MsgBox "Abortiing! no comments exist on worksheet!"
Exit Sub
End If

For Each copycell In commentsrng
If copycell.Offset(0, 4).Value = "" Then
copycell.Offset(0, 4).Value = copycell.Comment.Text
End If
Next copycell

Application.ScreenUpdating = True
End Sub
Hope you enjoy the video and don't forget to give your valuable feedback.
Also don't forget to subscribe to my channel. Thank you!