Maintain acell comment while updating a report

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Hello to all:

I've been thinking this one over for a while and I'm not quite sure what would be the best way to carry this out.

I provide someone with a standard report on a weekly basis and the only formulas contained in the report are subtotals (columns A:G). Basically I pull this data from access, place it into excel, subtotal the file and send it on its way. Now, the user has been inserting comments into cells (which are in outside of the report itself-cells in column F)) and doesn't want to lose them. Although, the amount of data will most likely increase from week to week, the primary names associated with the report will never change.

Any suggestions?

Thanks,

Anthony
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Would the target location of the comments shift week-to-week? I.e. would a comment in [F100] always need to go back to [F100]? Or would the comment in [F100] this week go into [F102] next week? If it varies, how do you know? (What's your key to knowing that [F100] this week is the same guy as [F102] next week?)
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Interesting point...consider the following:

The report will always be from A:D - after week one, the user inserts a comment in cell E4 based upon the information in that row...week 2 comes along and because additional information has been added to the report -the comment in cell E4 is referencing to the wrong line item.

I'm looking for a way to allow the initial comment to refer back to the original line item once the report is updated.

Is this possible?

Thanks,

Anthony
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
I can't think of a way that doesn't involve VBA. If that's ok, then need to know the answer to my question on how you'd know it's no longer the same. What would you need to Find on the new sheet that was in the old sheet so that you can match up and copy over the comment?
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465

ADVERTISEMENT

VBA is always welcome...please refer to the below table. Notice that during week one, the user inserted a comment into cell E4 - now week two comes along and Gary has reported more information so that his Math business in Pallisades Park will now be placed in the 5th row and being that his comment in E$ will remain static, at week 2 it will refer to the wrong line item.

Does that make sense?

Thanks.....Anthony
Book15
ABCDEFGHI
1Week1Week2
2Comment
3RepDistrictSubjectPotentialRepDistrictPotential
4GaryPalisadesParkMath$50,000TestGarryNutleyReading$60,000
5GaryParsippanyMusic$40,000GarryPalisadesParkMath$50,000
6GaryRochelleParkReading$30,000GarryParsippanyMusic$40,000
7GaryVernonTwpScience$20,000GarryRochelleParkReading$30,000
8GaryWestPatersonSocialStudies$10,000GarryVernonTwpScience$20,000
9RepTotal$150,000GarryWestPatersonSocialStudies$10,000
10RepTotal$210,000
Sheet1
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Anthony,

The following code should work. There are some limitations, which I added in as comments in the code. I apologize, but I just don't have time to work my through them right now.

I didn't know several particulars like whether the sheets are in the same workbook, what the sheet names would be etc. So I tried to write this to be as flexible as possible. Obviously, you could edit this to Set wsSource and wsTarget in the code for the user if desired. Note that if the worksheets are in different books, they'll need to look in All Open Workbooks for the macro.

Anyhow, I hope this helps.

<font face=Courier New><SPAN style="color:#00007F">Dim</SPAN> wsSource <SPAN style="color:#00007F">As</SPAN> Worksheet, wsTarget <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">Dim</SPAN> wbSource <SPAN style="color:#00007F">As</SPAN> Workbook, wbTarget <SPAN style="color:#00007F">As</SPAN> Workbook

<SPAN style="color:#00007F">Sub</SPAN> CopyComments()
<SPAN style="color:#007F00">' ---------------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">' Copies comments from one worksheet to another</SPAN>
<SPAN style="color:#007F00">' based on matching values in specified columns in both sheets</SPAN>
<SPAN style="color:#007F00">' LIMITATIONS:</SPAN>
<SPAN style="color:#007F00">'   1. The columns where the comments are located are the same</SPAN>
<SPAN style="color:#007F00">'      on both worksheets.</SPAN>
<SPAN style="color:#007F00">'   2. The columns being matched on both sheets are the same on</SPAN>
<SPAN style="color:#007F00">'      both worksheets.</SPAN>
<SPAN style="color:#007F00">'   3. Neither the matching columns range nor the comments columns range</SPAN>
<SPAN style="color:#007F00">'      can comprise more than one area. (The columns must be side-by-side.)</SPAN>
<SPAN style="color:#007F00">'      Example: You cannot set strcMatchCols = "A:A,C:D".</SPAN>

<SPAN style="color:#007F00">'                                                       - Greg</SPAN>
<SPAN style="color:#007F00">' ---------------------------------------------------------------------</SPAN>

    <SPAN style="color:#00007F">Const</SPAN> strcCommentCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "E:F"      <SPAN style="color:#007F00">' where the comments are</SPAN>
    <SPAN style="color:#00007F">Const</SPAN> strcMatchCols <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A:C"        <SPAN style="color:#007F00">' columns that need to match up</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> strMsg <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, comSource <SPAN style="color:#00007F">As</SPAN> Comment
    <SPAN style="color:#00007F">Dim</SPAN> rngSourceMatch <SPAN style="color:#00007F">As</SPAN> Range, rngTargetMatch <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> rngSourceComment <SPAN style="color:#00007F">As</SPAN> Range, rngTargetComment <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> rngSourceComments <SPAN style="color:#00007F">As</SPAN> Range, rngTargetComments <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> varValues() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, rngMatch <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> bytCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, booFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, strFirstAddr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> wsSource <SPAN style="color:#00007F">Is</SPAN> Nothing _
    <SPAN style="color:#00007F">Or</SPAN> wsSource <SPAN style="color:#00007F">Is</SPAN> Nothing <SPAN style="color:#00007F">Then</SPAN>
        strMsg = "Is Worksheet: " & ActiveSheet.Name & vbCr & _
                 "In Workbook:  " & ActiveWorkbook.Name & vbCr & vbCr & _
                 "Where you want to copy comments FROM?  " & vbCr & vbCr & _
                 "If not, click [NO] and select the correct sheet."
        <SPAN style="color:#00007F">If</SPAN> vbNo = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Source") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wsSource = ActiveSheet
        <SPAN style="color:#00007F">Set</SPAN> wbSource = ActiveWorkbook
        MsgBox "Now click on the sheet where you want to copy to.", vbInformation
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">If</SPAN> wsTarget <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> _
    <SPAN style="color:#00007F">Or</SPAN> wsTarget <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        strMsg = "Is Worksheet: " & ActiveSheet.Name & vbCr & _
                 "In Workbook:  " & ActiveWorkbook.Name & vbCr & vbCr & _
                 "Where you want to copy comments TO?  " & vbCr & vbCr & _
                 "If not, click [NO] and select the correct sheet."
        <SPAN style="color:#00007F">If</SPAN> vbNo = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Target") <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wsTarget = ActiveSheet
        <SPAN style="color:#00007F">Set</SPAN> wbTarget = ActiveWorkbook
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    strMsg = "Source: " & wsSource.Name & " in " & wbSource.Name & vbCr & vbCr & _
             "Target: " & wsTarget.Name & " in " & wbTarget.Name
    <SPAN style="color:#00007F">If</SPAN> vbCancel = MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm Again Please") <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wsSource = <SPAN style="color:#00007F">Nothing</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wbSource = <SPAN style="color:#00007F">Nothing</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wsTarget = <SPAN style="color:#00007F">Nothing</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> wbTarget = <SPAN style="color:#00007F"><SPAN style="color:#00007F">Not</SPAN>hing</SPAN>
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> rngSourceComments = wsSource.Range(strcCommentCols)
    <SPAN style="color:#00007F">Set</SPAN> rngTargetComments = wsTarget.Range(strcCommentCols)
    <SPAN style="color:#00007F">Set</SPAN> rngSourceMatch = wsSource.Range(strcMatchCols)
    <SPAN style="color:#00007F">Set</SPAN> rngTargetMatch = wsTarget.Range(strcMatchCols)
    
    <SPAN style="color:#00007F">ReDim</SPAN> varValues(rngSourceMatch.Columns.Count)
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> comSource <SPAN style="color:#00007F">In</SPAN> wsSource.Comments
        <SPAN style="color:#00007F">If</SPAN> Not Intersect(comSource.Parent, rngSourceComments) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> rngMatch = <SPAN style="color:#00007F"><SPAN style="color:#00007F">Not</SPAN>hing</SPAN>
            booFound = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">For</SPAN> bytCol = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(varValues)
                varValues(bytCol) = rngSourceMatch(comSource.Parent.Row, bytCol)
            <SPAN style="color:#00007F">Next</SPAN> bytCol
            <SPAN style="color:#00007F">Set</SPAN> rngMatch = rngTargetMatch.Columns(1).Find(varValues(1), LookAt:=xlWhole, LookIn:=xlValues)
            <SPAN style="color:#00007F">If</SPAN> Not rngMatch <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Not</SPAN>hing <SPAN style="color:#00007F">Then</SPAN> strFirstAddr = rngMatch.Address
            <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Not rngMatch <SPAN style="color:#00007F">Is</SPAN> Nothing
                booFound = <SPAN style="color:#00007F">True</SPAN>
                <SPAN style="color:#00007F">For</SPAN> bytCol = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(varValues)
                    <SPAN style="color:#00007F">If</SPAN> rngMatch.Offset(, bytCol - 1).Value <> varValues(bytCol) <SPAN style="color:#00007F">Then</SPAN>
                        booFound = <SPAN style="color:#00007F">False</SPAN>
                        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">Next</SPAN> bytCol
                <SPAN style="color:#00007F">If</SPAN> booFound <SPAN style="color:#00007F">Then</SPAN>
                    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>
                <SPAN style="color:#00007F">Else</SPAN>
                    <SPAN style="color:#00007F">Set</SPAN> rngMatch = rngTargetMatch.Columns(1).FindNext(rngMatch)
                    <SPAN style="color:#00007F">If</SPAN> rngMatch.Address = strFirstAddr <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Do</SPAN>
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">Loop</SPAN>
            <SPAN style="color:#00007F">If</SPAN> booFound <SPAN style="color:#00007F">Then</SPAN>
                comSource.Parent.Copy
                rngTargetComments.Cells(rngMatch.Row, comSource.Parent.Column - rngTargetComments.Columns(1).Column + 1).PasteSpecial _
                    Paste:=xlPasteComments, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=<SPAN style="color:#00007F">False</SPAN>
                Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> comSource
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Regards,
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022

ADVERTISEMENT

Just noticed - you can delete the Dim statement

<font face=Courier New>    <SPAN style="color:#00007F">Dim</SPAN> rngSourceComment <SPAN style="color:#00007F">As</SPAN> Range, rngTargetComment <SPAN style="color:#00007F">As</SPAN> Range

They're left over from a failed approach and I forgot to take that out after the code below no longer referenced them.
 

Anthony G.

Active Member
Joined
Mar 24, 2002
Messages
465
Thank You for taking the time out to write this very interesting code!!

I made the following adjustments to your code:

Const strcCommentCols As String = "O:O"
Const strcMatchCols As String = "B:D"
Dim rngSourceComment As Range, rngTargetComment As Range-Deleted


The sheets are in the same workbook and are named Sheet1 and Sheet2. (There are additional Worksheets in the Worbook that do not apply to the task that I am attempting to carry out).

After reading the code, this is my interpretation...Sheet1 = Week1 data / Sheet2 = Week2 data and the only difference between the two sheets is that Sheet 2 will contain more rows and NO COMMENTS. Therefore, the intention of the code is to copy the comments from Column O:O which will recognize them from the data in B:D...at which time those comments will then be pasted into Sheet2, column O:O.

Now, when I ran the code - this is my first prompt:

Is Worksheet: Sheet 1
In Workbook: Test
Where you want to copy comments TO?

If not, click [NO] and select the correct sheet.
If I click NO, then the code ends, however if I clickk Yes...next comes the second prompt:

Source: Sheet1 in Test
Target: Sheet1 in Test

I click yes and it simply re-pastes the comments in Sheet1. I thought that perhaps it would copy the comments to Sheet2 based upon the relationship it gathered from columns B:D in Sheet1.

Actually, when I first pasted the code and made the column adjustments, I neglected to remove Dim rngSourceComment As Range, rngTargetComment As Range...and I was then prompted to select the Worksheet that I wanted the data to be copied to, but the hour-glass remained on and wouldn't let me navigate between sheets.

It must have taken you quite some time to write this, so I wanted to be thorough in my explanation in an attempt for you to have an understanding as to what I might be over looking.

Look forward to hearing from you!

Anthony
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
The way it's written you have to run it twice. The first time you need to have the sheet you want to copy FROM (week 1) active (on top). The second time you need to have the sheet you want to copy TO (week 2) active. You have to have the right sheet selected before the first/second run. Once it's running, you can't select. The best way to really implement this would be via a UserForm and RefEdit objects, but I didn't have time for anything quite that pretty.

Since wbSource, wbTarget, wsSource and wsTarget are declared outside of the procedure, they do not get cleared. You can clear them by running this again and when you get to the prompt entitled "Confirm Again Please" click [Cancel] and it will clear these object variables and you can start over.

Also, I should have put the following four lines right before the End Sub

Set wsSource = Nothing
Set wbSource = Nothing
Set wsTarget = Nothing
Set wbTarget = Nothing
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,692
Members
425,230
Latest member
DzOus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top