• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need VBA code for copy paste comments from one workbook to another workbook

K

Kalai

New Member
Hi,

I have two workbooks with the same name of the sheets. I need to copy the comments from one workbook to another workbook with the respective sheet names. For example, i have to copy the comments from Sheet1 (Workbook1) to Sheet1(Workbook2). Could any one help me to short out this by VBA coding?

Thanks in advance.
Kalai
 
Ranges may vary. But generally its like this assuming both files are open

Code: 
Sub CopyComments ()
Worksheets("Sheet1").Select
Range("A1").Copy
Windows("Workbook2").Activate
Worksheets("Sheet1").Select
Range("A1").PasteSpecial xlPasteComments
End Sub
 
Hi Chirayu,

Thanks for your help. I have more then 70 sheets in the workbook. So is there any other code using loop to do this action?

Thanks,
Kalai
 
Check the zip file & read the instructions. Macro code is given below.

Code: 
Sub CopyComments()

'Made by Chirayu Walawalkar 15-Jun-2015

Dim SourceWB As String
Dim DestinationWB As String
Dim MyRng As String
Dim MySht As String

SourceWB = ActiveWorkbook.Name

'Open Destination Workbook
'-------------------------
MsgBox "Please open the Destination Workbook", vbInformation, ""

FileToOpen = Application.GetOpenFilename _
(Title:="Please open the Destination Workbook", _
FileFilter:="Excel Files *.xlsx (*.xlsx),") 'Change .xlsx to .xls or .xlsm basis your requirement
If FileToOpen = False Then
  MsgBox "No file specified. Macro will now exit.", vbCritical, ""
  Exit Sub
Else
  Workbooks.Open Filename:=FileToOpen
  DestinationWB = ActiveWorkbook.Name
End If
  Windows(SourceWB).Activate
 
'The loop code for pasting comments
'----------------------------------
  For Each ws In Workbooks(SourceWB).Worksheets
  ws.Select
  Range("B2:B6").Select
  'Change the above range to whatever you want or use below code for dynamic range
  'Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Select
  MyRng = Selection.Address
  MySht = ActiveSheet.Name
  Selection.Copy
  Windows(DestinationWB).Activate
  Sheets(MySht).Select
  Range(MyRng).Select
  Selection.PasteSpecial xlPasteComments
  Application.CutCopyMode = False
  Range("A1").Select
  Windows(SourceWB).Activate
  Range("A1").Select
  Next ws

'End Macro
'---------
MsgBox "Comments copied", vbInformation, ""

End Sub
 

Attachments

  • Copy Comments.zip
    29.6 KB · Views: 17
Hi Chirayu,

Thanks again, I have tried this code in my working file. I got an error message

"Run time error '1004':
Select method of worksheet class failed".

I Couldn't understand about this error message. Could you please help me to sort out this?

Thanks,
Kalai
 
Check if the sheet is present in both files. As I said in the instruction. If the sheet ain't there then it won't work because I haven't actually written that in :p.

If it is present then check sheet names. If it still doesn't work then please attach file.
 
Hi,

Thanks for that. Both are same file with different name. I will try one more time.

Thanks
Kalai
 
You must log in or register to reply here.
Back
Top