Copy and paste as a comment from one Excel file to other Excel file

[BBCC0000]

Hi everyone,

I'm not sure Excel VBA can do this, so I'm posting it to ask. Hope you guys can spend a little time to help me on this

First step:
I have this Excel file (File A) which contains Number, Code and Your Name, and the name in cell E3. The name could be changed periodically (variable).

Second step:
I have another Excel file (File B) which contains many rows.

Third step:
I want file A copy their Code and paste to the matched Number of file B respectively as a COMMENT.
The name of the comment is from the file B cell E2.

I really appreciate if there is anyone who knows about this. Thanks for your help in advance!

 

[Georgiboy]

You have followed me down to post 8 with various different amendments to the code, but now you are quoting post 3. Were you not getting errors before or had you not tested any of the code?

What line do you get the error on?

 

[BBCC0000]

You have followed me down to post 8 with various different amendments to the code, but now you are quoting post 3. Were you not getting errors before or had you not tested any of the code?

What line do you get the error on?

Because I'm thinking the post 3 macro first, after that only use post 8 macro...

Not sure which line, but it only shows Subscript Out of Range".

 

[Georgiboy]

The code in post 9 adds the comments and then reorders them in the same macro, have you tried that code?

If you get an error click the debug button and it should highlight the error line in yellow. You can then post the yellow line of code here.

 

[BBCC0000]

The code in post 9 adds the comments and then reorders them in the same macro, have you tried that code?

If you get an error click the debug button and it should highlight the error line in yellow. You can then post the yellow line of code here.

I have also tried post 9 code, it shows the same error but without highlighting the line in yellow....

 

[Georgiboy]

Open up the code window, put your cursor somewhere in the code you want to test, press F8, the code should run one line at a time each time you press F8. When you get the error, that will be the line that is causing the problem.

As a guess maybe the below lines:
Maybe the workbook name or sheet names are not right?
Are both of the workbooks below open when you run the code?
Is the code being run from one of the below workbooks?

    Set ws1 = Workbooks("Book1.xlsx").Sheets("Sheet1")
    Set ws2 = Workbooks("Book2.xlsx").Sheets("Sheet1")

 

[BBCC0000]

Open up the code window, put your cursor somewhere in the code you want to test, press F8, the code should run one line at a time each time you press F8. When you get the error, that will be the line that is causing the problem.

As a guess maybe the below lines:
Maybe the workbook name or sheet names are not right?
Are both of the workbooks below open when you run the code?
Is the code being run from one of the below workbooks?

    Set ws1 = Workbooks("Book1.xlsx").Sheets("Sheet1")
    Set ws2 = Workbooks("Book2.xlsx").Sheets("Sheet1")

Oh the yellow one stopped at ws1, because that file is a macro-enabled worksheet, so I put .xlsm
Does it matter? I tried for .xlsx too but it doesn't work and shows the same error

"Is the code being run from one of the below workbooks?" - I'm doing this, so that's the reason it wouldn't work?

 

[Georgiboy]

So does it work now or not?

Are both of the workbooks open when you run the code?

 

[BBCC0000]

So does it work now or not?

Are both of the workbooks open when you run the code?

Still not working, I have already open both workbooks when I run the code

 

[Georgiboy]

And in the below, you are changing all of the orange parts to suit your situation, for example: you are not leaving it as 'Book1.xlsm' if you workbook is named 'MyWorkbookA' for example?
Also, are you updating the sheet names from 'Sheet1' to the names of the sheets you are using?

I just created two files with the code in 'File B' and run the below and it worked fine:

Sub test()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rng As Range, rCell As Range, str As String, nm As String
    Dim cTop As Long, cLeft As Long
   
    Set ws1 = Workbooks("File A.xlsx").Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet1")
   
    Set rng = ws2.Range("A2:A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
    nm = ws1.Range("E3")

    For Each rCell In rng
        If Application.CountIf(ws1.Range("A:A"), rCell) > 0 Then
            str = Application.VLookup(rCell, ws1.Range("A:B"), 2, 0)
            With rCell.Offset(, 3)
                If Not .Comment Is Nothing Then .Comment.Delete
                .AddComment nm & vbNewLine & str
                With .Comment.Shape.TextFrame
                    .Characters(1, Len(nm)).Font.Bold = True
                    .AutoSize = True
                End With
            End With
        End If
    Next rCell
   
    Set rng = rng.Resize(, 5)
    cTop = rng.SpecialCells(xlCellTypeComments)(1).Comment.Shape.Top
    cLeft = ws2.Range("G1").Left
    For Each rCell In rng.Cells
        With rCell
            If Not .Comment Is Nothing Then
                .Comment.Visible = True
                .Comment.Shape.Top = cTop
                .Comment.Shape.Left = cLeft
                cTop = .Comment.Shape.Top + .Comment.Shape.Height
            End If
        End With
    Next rCell
End Sub

The reason I changed the below:

Set ws2 = ThisWorkbook.Sheets("Sheet1")

Is because of the the question i asked a few posts ago:

Is the code being run from one of the below workbooks?