Copying comments from one cell to another

[Wayne]

Is there a VB way to copy the comments from a cell in one
workbook and attach them to a cell in a second workbook?

Wayne

 

[Tom Ogilvy]

workbooks("Book2.xls").Worksheets(2).Range("B9").NoteText Text:= _
Workbooks("Book1.xls").Worksheets(1).Range("A5").NoteText

See help on notetext if the length is greater than 255

 

[Wayne]

Thanks Tom! Looks like about 95% of what i'll need.

I'd like to do this throughout a range of cells. Here is
the code I'm using to step through the range to color the
backgound. Each cell contains a paste link reference to
the source file. I'm hoping I can include code like yours
as part of the "For each cell in range" loop:

Sub UpdateStoplights()
Application.ScreenUpdating = False
Range("stoplights").Select
Dim Cel As Range
Dim val As Integer
For Each Cel In Range("stoplights")
val = Cel.Value
Select Case val
Case 1
Cel.Interior.ColorIndex = 3
Cel.Font.ColorIndex = 3
Case 2
Cel.Interior.ColorIndex = 6
Cel.Font.ColorIndex = 6
Case 3
Cel.Interior.ColorIndex = 4
Cel.Font.ColorIndex = 4
Case 4
Cel.Interior.ColorIndex = 5
Cel.Font.ColorIndex = 5
Case Else
Cel.Interior.ColorIndex = 2
Cel.Font.ColorIndex = 2
End Select
Next

Thanks again.

Wayne

 

[Tom Ogilvy]

Since you don't say where to put the comments, here is illustrative code:

Sub UpdateStoplights()
Application.ScreenUpdating = False
Range("stoplights").Select
Dim Cel As Range
Dim i as Long
Dim val As Integer
i = 1
For Each Cel In Range("stoplights")
workbooks("Book2.xls").Worksheets(2) _
.Range("B9")(i).NoteText Text:= _
cel.NoteText
i = i + 1
val = Cel.Value
Select Case val
Case 1
Cel.Interior.ColorIndex = 3
Cel.Font.ColorIndex = 3
Case 2
Cel.Interior.ColorIndex = 6
Cel.Font.ColorIndex = 6
Case 3
Cel.Interior.ColorIndex = 4
Cel.Font.ColorIndex = 4
Case 4
Cel.Interior.ColorIndex = 5
Cel.Font.ColorIndex = 5
Case Else
Cel.Interior.ColorIndex = 2
Cel.Font.ColorIndex = 2
End Select
Next

 

[Guest]

I need to copy them from a cell in a second worksheet,
the address of which is in the target cell as a Paste
link, e.g "\\FIL-NW02-06

\MPTTechTeamLeaders\8_Tech_Team_Scorecard\Reddy Team
Scorecards\[2005_Define_Scorecard_Hixson.xls"

So how might that work? Is there a way to get that
pathname and useit where you have the "Book.xls"
referrence for example?

Wayne

 

[Tom Ogilvy]

You would have to open it

Dim bk as Workbook
set bk = workbooks.Open( Range(SomeCell).Value)

then
workbooks("Book2.xls").Worksheets(2) _
.Range("B9")(i).NoteText Text:= _
cel.NoteText

would be

bk.Worksheets(2) _
.Range("B9")(i).NoteText Text:= _
cel.NoteText
--
Regards,
Tom Ogilvy

I need to copy them from a cell in a second worksheet,
the address of which is in the target cell as a Paste
link, e.g "\\FIL-NW02-06

\MPTTechTeamLeaders\8_Tech_Team_Scorecard\Reddy Team
Scorecards\[2005_Define_Scorecard_Hixson.xls"

So how might that work? Is there a way to get that
pathname and useit where you have the "Book.xls"
referrence for example?

Wayne

-----Original Message-----
Since you don't say where to put the comments, here is illustrative code:

Sub UpdateStoplights()
Application.ScreenUpdating = False
Range("stoplights").Select
Dim Cel As Range
Dim i as Long
Dim val As Integer
i = 1
For Each Cel In Range("stoplights")
workbooks("Book2.xls").Worksheets(2) _
.Range("B9")(i).NoteText Text:= _
cel.NoteText
i = i + 1
val = Cel.Value
Select Case val
Case 1
Cel.Interior.ColorIndex = 3
Cel.Font.ColorIndex = 3
Case 2
Cel.Interior.ColorIndex = 6
Cel.Font.ColorIndex = 6
Case 3
Cel.Interior.ColorIndex = 4
Cel.Font.ColorIndex = 4
Case 4
Cel.Interior.ColorIndex = 5
Cel.Font.ColorIndex = 5
Case Else
Cel.Interior.ColorIndex = 2
Cel.Font.ColorIndex = 2
End Select
Next

--
Regards,
Tom Ogilvy


.