Need advise on code

[AlexanderBB]

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

Found this on line and It may do just what I want - copy a comment from one workbook to another.
But I'm a bit wary - what comment is affected? I don't want to do all just some of them, one at a time.
And do I want SkipBlanks, Transpose etc as shown?
Any help appreciated,

 

[jolivanes]

Re: "may do just what I want"
Was the result not what you anticipated when you tried it on a copy of your workbook(s)?

 

[AlexanderBB]

Maybe someone knows what that code will do ?

 

[jolivanes]

The question is not what that code will do but what do you want to achieve.
You have to explain to the helpers what you would like to happen.

From: XlPasteType enumeration (Excel)
Name: xlPasteComments
Value: -4144
Description: Comments are pasted

 

[AlexanderBB]

Well I did say "I want - copy a comment from one workbook to another."
What more so you think is needed ?
I could do and see the end result but would prefer to know what to expect first, and any implications for the paste special instructions.

 

[jolivanes]

That's why we all should know to try supplied code on a copy, or in your case copies, of your original workbook(s)
You have these in front of you, we don't. If you do that, you can compare a before and after which we can't do of course.

You can expect comments to be pasted.

 

[Alex Blakenburg]

As jolivanes has pointed out, without specifics we can only give you very general guidance.
Here is a wordier version of the code you have.
You need to specify the source workbook, sheet and range to copy from and then do the same for the destination.
I don't see how copied comments are going to make sense unless you copy them to the same relative location in the destination.

Sub CopyComments()
    Dim wbSrc As Workbook, wbDest As Workbook
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim rngSrc As Range, rngDest As Range
   
    Set wbSrc = ThisWorkbook
    Set wbDest = Workbooks("Workbook2.xlsx")
   
    Set shtSrc = wbSrc.Worksheets("HR&Finance")
    Set shtDest = wbDest.Worksheets("Budget")
   
    Set rngSrc = shtSrc.UsedRange
    Set rngDest = shtDest.Range(rngSrc.Cells(1).Address)        ' Copy to the same cell location in the destination worksheet
   
    rngSrc.Copy
    rngDest.PasteSpecial Paste:=xlPasteComments
   
    Application.CutCopyMode = False
End Sub

 

[AlexanderBB]

Thank you Alex, I'll paste that into a module and keep it.
I has just finished doing them all by hand as I thought I'd not get a useful reply here.
Yours is a lot easier to follow, but I don't see where the target range is specified. Only UsedRange whatever that is??
Google should tell me what Application.CutCopyMode is. And yes they are going to the same location from xlsm file to xlsx.
I had already copied the sheet but there were some last minute corrections. All good fun..
Cheers

 

[Alex Blakenburg]

It would be better to provide the range you want to use rather than rely on UsedRange.
(this will mostly mean finding the LastRow and LastColumn in the code)
UsedRange is a rectangular range based on what Excel thinks is the first and last row & column used.
Unfortunately this may be different to what you think is the UsedRange since Excel will consider things like formatting to be "Used Cells".

So although most people assume it starts in A1 that may not be the case.
If you haven't actually got something in Row 1 somewhere it may not start at Row 1 and if you don't have something in Column A it may not start in Column A.

Paste only needs the top left corner of where you want to paste it and since we want to paste it in the same location it came from then
shtSrc.UsedRange.Cells(1).Address gets cell 1 of the used range being the top left corner and Address get the absolute address in the format $A$1 or $B$2 or whatever is the first cell in the used range.
We can then use that for the Destination range.

 

[AlexanderBB]

I'd not come across Used Range before. So basically it's everything in the sheet?
Your code in msg4 would copy every comment (Note?) in thisworkbook to workbook2.
If only one comment was involved Set rngSrc.Range("A25") would be right. As you said specify the range.
Will run some tests but think I've got it. Thanks for the rundown.