How do I copy a row of Excel comments from one sheet on a workbook to another sheet

Hi,

I have a problem. I will simplify it thusly. I have two workbooks opened, Book1 and Book2. Book2 is macro enabled. Book1 is not macro enabled. I want to copy a row of cells, each of which contain comments, to another worksheet on Book1. Let's say, my comments are located on A9 to F9 on Sheet1 of Book1. I want to transfer or copy those comments to particular cells on Sheet 2 of Book1. Let's say, G5, L12, D4, D13, P2, B13. 6 transfers altogether. What's the VBA code to do this? I am guessing some sort of for each loop.

Paul Carr

Hi maxpit, Thank you for your quick reply. Your code works. However, I think I should clarify what I want. On book1, I have a list of dates in column A of sheet1, formatted for each day of September. (A2 to A31) "1 September 2023", "2 September 2023" and so on. In addition to sheet1, book1 also contains a sheet for each day of the current month, entitled, "1 September 2023", "2 September 2023". What I want to do is transfer or copy a range of 6 comments, in columns b, c, d, e, f and g that I enter beside each date on sheet1 to specific cells on its corresponding sheet with the same date. For example, I enter comments into cells B2:G2 beside the date, "1 September 2023" that I enter into A2. I want to transfer them to specific cells on the corresponding sheet named "1 September 2023", let's say to specific cells "H13:H18".

I'm pretty sure that a for each loop statement will help me cut down the amount of VBA code considerably.