Maintain acell comment while updating a report

Anthony G. · Jan 8, 2004

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

Greg Truby · Jan 8, 2004

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. · Jan 8, 2004

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 · Jan 8, 2004

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. · Jan 8, 2004

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

A

B

C

D

E

F

G

H

I

1

Week1

Week2

2

Comment

3

Rep

District

Subject

Potential

Rep

District

Potential

4

Gary

PalisadesPark

Math

$50,000

Test

Garry

Nutley

Reading

$60,000

5

Gary

Parsippany

Music

$40,000

Garry

PalisadesPark

Math

$50,000

6

Gary

RochellePark

Reading

$30,000

Garry

Parsippany

Music

$40,000

7

Gary

VernonTwp

Science

$20,000

Garry

RochellePark

Reading

$30,000

8

Gary

WestPaterson

SocialStudies

$10,000

Garry

VernonTwp

Science

$20,000

9

RepTotal

$150,000

Garry

WestPaterson

SocialStudies

$10,000

10

RepTotal

$210,000

Sheet1

Greg Truby · Jan 9, 2004

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.

Dim wsSource As Worksheet, wsTarget As Worksheet
Dim wbSource As Workbook, wbTarget As Workbook

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

' - Greg
' ---------------------------------------------------------------------

 Const strcCommentCols As String = "E:F" ' where the comments are
 Const strcMatchCols As String = "A:C" ' columns that need to match up

 Dim strMsg As String, comSource As Comment
 Dim rngSourceMatch As Range, rngTargetMatch As Range
 Dim rngSourceComment As Range, rngTargetComment As Range
 Dim rngSourceComments As Range, rngTargetComments As Range
 Dim varValues() As Variant, rngMatch As Range
 Dim bytCol As Byte, booFound As Boolean, strFirstAddr As String

 If wsSource Is Nothing _
 Or wsSource Is Nothing Then
 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."
 If vbNo = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Source") Then Exit Sub
 Set wsSource = ActiveSheet
 Set wbSource = ActiveWorkbook
 MsgBox "Now click on the sheet where you want to copy to.", vbInformation
 Exit Sub
 End If
 If wsTarget Is Nothing _
 Or wsTarget Is Nothing Then
 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."
 If vbNo = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Target") Then Exit Sub
 Set wsTarget = ActiveSheet
 Set wbTarget = ActiveWorkbook
 End If
 strMsg = "Source: " & wsSource.Name & " in " & wbSource.Name & vbCr & vbCr & _
 "Target: " & wsTarget.Name & " in " & wbTarget.Name
 If vbCancel = MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm Again Please") Then
 Set wsSource = Nothing
 Set wbSource = Nothing
 Set wsTarget = Nothing
 Set wbTarget = Nothing
 Exit Sub
 End If

 Set rngSourceComments = wsSource.Range(strcCommentCols)
 Set rngTargetComments = wsTarget.Range(strcCommentCols)
 Set rngSourceMatch = wsSource.Range(strcMatchCols)
 Set rngTargetMatch = wsTarget.Range(strcMatchCols)

 ReDim varValues(rngSourceMatch.Columns.Count)

 For Each comSource In wsSource.Comments
 If Not Intersect(comSource.Parent, rngSourceComments) Is Nothing Then
 Set rngMatch = Nothing
 booFound = False
 For bytCol = 1 To UBound(varValues)
 varValues(bytCol) = rngSourceMatch(comSource.Parent.Row, bytCol)
 Next bytCol
 Set rngMatch = rngTargetMatch.Columns(1).Find(varValues(1), LookAt:=xlWhole, LookIn:=xlValues)
 If Not rngMatch Is Nothing Then strFirstAddr = rngMatch.Address
 Do While Not rngMatch Is Nothing
 booFound = True
 For bytCol = 2 To UBound(varValues)
 If rngMatch.Offset(, bytCol - 1).Value <> varValues(bytCol) Then
 booFound = False
 Exit For
 End If
 Next bytCol
 If booFound Then
 Exit Do
 Else
 Set rngMatch = rngTargetMatch.Columns(1).FindNext(rngMatch)
 If rngMatch.Address = strFirstAddr Then Exit Do
 End If
 Loop
 If booFound Then
 comSource.Parent.Copy
 rngTargetComments.Cells(rngMatch.Row, comSource.Parent.Column - rngTargetComments.Columns(1).Column + 1).PasteSpecial _
 Paste:=xlPasteComments, _
 Operation:=xlNone, _
 SkipBlanks:=False, _
 Transpose:=False
 Application.CutCopyMode = False
 End If
 End If
 Next comSource
End Sub

Regards,

Greg Truby · Jan 9, 2004

Just noticed - you can delete the Dim statement

 Dim rngSourceComment As Range, rngTargetComment As 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. · Jan 9, 2004

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 · Jan 9, 2004

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

Anthony G. · Jan 10, 2004

Worked like a charm Greg....very impressed!!

Many Thanks!

Anthony

Maintain acell comment while updating a report

Anthony G.

Active Member

Excel Facts

Greg Truby

MrExcel MVP

Anthony G.

Active Member

Greg Truby

MrExcel MVP

Anthony G.

Active Member

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

Anthony G.

Active Member

Greg Truby

MrExcel MVP

Anthony G.

Active Member

Similar threads

Forum statistics

Share this page

Maintain acell comment while updating a report

Active Member

Excel Facts

MrExcel MVP

Active Member

MrExcel MVP

Active Member

MrExcel MVP

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock