Copy Comments from Sheet to Sheet
Closed
DCecil07
Posts
30
Registration date
Monday 15 March 2010
Status
Member
Last seen
15 September 2010
-
7 May 2010 à 09:49
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 7 May 2010 à 12:46
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 7 May 2010 à 12:46
Related:
- Copy Comments from Sheet to Sheet
- How to copy data from one excel sheet to another - Guide
- Little alchemy cheat sheet - Guide
- Excel hyperlink to another sheet - Guide
- How to copy data to multiple worksheets in Excel - Guide
- Excel move data from one sheet to another - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
7 May 2010 à 10:24
7 May 2010 à 10:24
Are you looking to not only show value but also comments from the cell ? So if a cell on master had a value and a comment, you want to show both ?
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
7 May 2010 à 12:08
7 May 2010 à 12:08
Ok first the bad news. If you update the comment in the original cell, the code will not update. This works for the first time only.
The closest I was able to make it more dynamic was if you add this routine to the sheet events
Function PeekThru(myLinkCell As Range, thisRow As Long, thisCol As Integer, mySheet As String) As Variant
On Error Resume Next
Cells(thisRow, thisCol).Comment.Delete
On Error GoTo 0
If (myLinkCell.Comment.Text <> "") Then
With Sheets(mySheet).Cells(thisRow, thisCol)
.AddComment
.Comment.Visible = False
.Comment.Text Text:=myLinkCell.Comment.Text
End With
End If
PeekThru = myLinkCell
End Function
The closest I was able to make it more dynamic was if you add this routine to the sheet events
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Application.CalculateFull
Application.EnableEvents = True
End Sub
DCecil07
Posts
30
Registration date
Monday 15 March 2010
Status
Member
Last seen
15 September 2010
7 May 2010 à 12:21
7 May 2010 à 12:21
Will this work for just one cell? Or does this work for the entire sheet?
This would need to grab comments from the entire sheet. Also, since this is a function, do I need to have a button call the function? Does it even require a button?
This would need to grab comments from the entire sheet. Also, since this is a function, do I need to have a button call the function? Does it even require a button?
rizvisa1
Posts
4478
Registration date
Thursday 28 January 2010
Status
Contributor
Last seen
5 May 2022
766
7 May 2010 à 12:46
7 May 2010 à 12:46
Instead of this
=IF(ISBLANK(Master!A1),"",Master!A1)
use like this
=PeekThru(Master!A1, Row(), Column(), "Detail")
"Detail" is the sheet name where you are entering this formula. If this is some other sheet, correct the name
=IF(ISBLANK(Master!A1),"",Master!A1)
use like this
=PeekThru(Master!A1, Row(), Column(), "Detail")
"Detail" is the sheet name where you are entering this formula. If this is some other sheet, correct the name
7 May 2010 à 11:01