Updated problem

A

alexm999

Some great replies before, but i need a bit more help.

Here's my code:
Windows("Deviation.xls").Activate
Range("C1").Activate
Selection.Copy
Windows("EVALUATION.xls").Activate
Application.CutCopyMode = False
Range("M9").AddComment
Range("M9").Comment.Visible = False
Range("M9").Comment.Text Text:="Alex:" & Chr(10) & ""

How do i copy text from one cell in the deviation.xls file and paste it
as a comment in the evaluation.xls file?
 
F

Frank Kabel

Hi
try something like the following:
sub foo()
dim wbk_source as workbook
dim wbk_target as workbook
dim wks_source as worksheet
dim wks_target as worksheet

set wbk_source = workbooks("Deviation.xls")
set wks_source = wbk_source.worksheets("Sheet1")
set wbk_target = workbooks("EVALUATION.xls")
set wks_target = wbk_target.worksheets("Sheet1")

with wks_target.range("M9")
.AddComment
.Comment.Visible = False
Comment.Text Text:="Alex:" & Chr(10) & _
wks_source.range("A1").value
end with
end sub
 
F

Frank Kabel

Hi
in this case you already have a comment in cell M9. Try the following
change:
.....
with wks_target.range("M9")
on error resume next
.AddComment
on error goto 0
.Comment.Visible = False
Comment.Text Text:="Alex:" & Chr(10) & _
wks_source.range("A1").value
end with
end sub
 
Top