Solved: copying comments

[Ger]

I'm using excell2003 dutch version.
Excuse my english.
Who can help me? I get every month a spreadsheet with data. By using comments i give an explanation of some data.The spreadsheet I get every month contains the the current month but also the expired months of the year. How can I get the comments of the past month in the new spreadsheet. I also opened this thread in the non english help. As i need it asap and the help inhere is quicker i started a thread in english to. In the non english thread i included a example.
http://www.vbaexpress.com/forum/showthread.php?t=25418

In test jan some figures have been commented by means of an observation. In test feb the figures of test jan are visible and the new figures over February including comment february. How do I get the comments from test jan in test feb.
Thanks in advance for the help.

Ger

[Bob Phillips]

A few questions.

Do you enter each value each month, or do you paste it from somewhere?
Is it possible to copy say the Jan sheet into the Feb workbbok, at least as an initial step before copying the comments?
Are thes comments meant to overwrite the Feb commenst, or be appended?

[Ger]

XLD,
I get each month a new sheet with the data. In april i get a sheet with the data from jan till april. The comments i made in the jan, feb and march sheet must be copied to the data in the april sheet. it is not possible to copy the data from the jan/feb or march sheet into the april sheet because there may be new rows added. In my example you can see that in feb row 4 is added. As the original sheet in feb already is from A1 to AE817 it is impossible to copy each value bij hand.
The comments of jan must be copied to the data over jan (column F in feb sheet). In march the comment of jan and feb must be copied to the march sheet (column F and H) etc. Comments will not be overwritten in this way.

Ger

[Ger]

I tried, using column A,B and D as key, to get the value incl comment using vert.zoeken (search vertical). But only the value appears and not the comment.
So i'm still copy by hand from one sheet to the other. Next week I get march then i must copy (by hand) the comment of january and february. this will take me a whole day.

please help.

[Ger]

As i get no respons i try again.
I get each month a worksheet "NSR". Using comments i explain some of the values. In february the worksheet contains the values of january(column G and H) and the new values of february (column (I and J). The columns B, C and E together are a unique key (put in column A).
I copy the worksheet with explainations of january to the sheet "tm" in february.

My question is: how can i get the comments in the sheet "tm" to the sheet "nsr".
Note: each month the new worksheet "NSR" will grow by 2 columns and ?? rows. In march i copy the worksheet feb, sheet "NSR" to the "tm" sheet and i need the comments from column G to J to be copied.

I tried vlookup but this will not copy the comments.


Please help



Ger

[Ger]

I get an error message for the if statement. What is wrong. I placed "end if" before case still error message, so i tried "end if" before end sub still error. The error message is "end if without blok if".


Sub Macro2()
'
' Macro2 Macro
' De macro is opgenomen op 12-3-2009 door ger.vranken.
'
Sheets("zml").Select
sq = Sheets("zml").Range("a1:g20")
sq2 = Sheets("tm").Range("a1:g20")
For j = 2 To 20
For jj = 1 To 1
If (sq(j, jj)) = (sq2(j, jj)) Then Select Case Copy

Case Copy
Range (sq2(j, jj + 6))
Selection.Copy
Range (sq(j, jj + 6))
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End


End Sub

[Bob Phillips]

[vba]

Sub Macro2()
'
' Macro2 Macro
' De macro is opgenomen op 12-3-2009 door ger.vranken.
'
Sheets("zml").Select
sq = Sheets("zml").Range("a1:g20")
sq2 = Sheets("tm").Range("a1:g20")
For j = 2 To 20
For jj = 1 To 1
If (sq(j, jj)) = (sq2(j, jj)) Then

Select Case Copy

Case Copy
Range (sq2(j, jj + 6))
Selection.Copy
Range (sq(j, jj + 6))
Selection.PasteSpecial Paste:=xlPasteComments, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Select
End If
End Sub
[/vba]

[Ger]

Stupid me to forget end select. Thanks Xld

Now i get an error on Range (sq2(j, jj + 6)) .

What i'm trijing is to copy the comments from sheet "tm" to the sheet "NSR".

Ger

[Bob Phillips]

What is Copy that the select works on, where is it set? And it is abad name!

[Ger]

with some help i finaly have a working macro. For those who interested her it is:

Sub OpmerkingenKopieeren()
' De macro is gemaakt op 12-3-2009 door Janique.Kronemeijer.
Sheets("tm").Select
Range("A2").Activate
'On Error GoTo FoutAfh
Do Until IsEmpty(ActiveCell)
zkwrd = ActiveCell
Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 32)).Copy
Sheets("NSR").Select
Columns("A:A").Find(zkwrd, LookIn:=xlValues).Activate
Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 32)).PasteSpecial xlPasteComments
Sheets("tm").Select
ActiveCell.Offset(1, 0).Activate
Loop
Application.CutCopyMode = False
Range("A2").Activate
Exit Sub
FoutAfh:
Sheets("tm").Select
Application.CutCopyMode = False
MsgBox "Op blad NSR ontbreekt de sleutel: " & zkwrd
End Sub