0

I wish to use VBA to add a note to a cell. This note should be the cell values from a different tab.

I have the tab - Current Total Breakdown. I wish to copy cells H5 and H6 in this tab into the note on cell K5 on the Monthly Totals tab. I would also like the cell value in H5 to be on one line and the value of H6 on another line.

For example, if H5 and H6 in the Current Total breakdown tab are equal to "Hi x 10" and "Hello x 20" respectively:

I would like K5 in Monthly Totals to have a comment saying:

Hi x 10
Hello x 20

as shown one below the other so i know they're from different cells.

I have to repeat this too. I know how to write I'm not very good with VBA but I know how to write the select, copy and paste and even add a note to a cell but not in the format I wish like above.

Not really sure at all how I would go about this so would greatly appreciate and help and if you could break the code down for me so I can edit it for my other ranges I need to apply it.

Code i've got so far (tried to record the macro; not 100% sure what the last line is doing). 

Sheets("Current Total breakdown").Select
Range("H5").Select
Selection.Copy
Sheets("Monthly Totals").Select
ActiveCell.Offset(-19, 8).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.AddComment
ActiveCell.Comment.Visible = False
ActiveCell.Comment.Text Text:=Chr(10) & "" & Chr(10) & ""
ActiveCell.Offset(16, -6).Range("A1").Select
Scott
  • 122
  • 1
  • 10
PGD15
  • 183
  • 2
  • 15
  • 2
    Can you please [edit your question](https://stackoverflow.com/posts/59293475/edit) to include the code you have so far? – cybernetic.nomad Dec 11 '19 at 20:21
  • @cybernetic.nomad added the code i've got so far (missed the sub and end sub both that is it. – PGD15 Dec 11 '19 at 20:42
  • 1) [Avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. 2) You can assign `"Range("H5").value & Chr(10) & Range("H6").value` either to a variable or directly to the comment's `text` – cybernetic.nomad Dec 11 '19 at 20:56
  • I understand its best to specify, but i tried to record the Macro as i'm not very good with VBA and don't need to use if often. If i understand correctly you're saying I should write the line you've provided me, select the cell K5 in my Monthly Totals tab, add a comment and paste the value? This should work? (away from my PC atm). – PGD15 Dec 11 '19 at 21:04

1 Answers1

0

No need to select anything:

Dim myComment as String
myComment = Sheets("Current Total breakdown").Range("H5").value & Chr(10) & Sheets("Current Total breakdown").Range("H6").Value
Sheets("Monthly Totals").Range("K5").AddComment.Text myComment
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31