copy comment from 1 worksheet and paste it on another.

infuture · #1 (**permalink**) 11-30-04, 02:44

Hi all, I'm working with a excel macro to copy a comment of a cell from 1 worksheet and pasting the comment on a cell in another worksheet. Then i was struck wif the .addcomment code. Please help and thanks. The code that i'm struck wif is as follows.

Dim tempstr1 As String 'variable for sheet 1
Dim tempstr2 As String 'variable for summary
Dim temprange1 As Integer 'cell no for sheet 1
Dim temprange2 As Integer 'cell no for summary
Dim tempcomment As String 'cell comment

temprange1 = 1 'Declaring cell 1 for sheet 1
Do Until (temprange1 > 5) 'Looping through the values to copy
Sheets("SHEET1").Select
Range("B" & temprange1).Select 'pulling out the value of cell from sheet 1
Application.CutCopyMode = False
tempstr1 = Selection.Value
'If range("B" & temprange1).Comment <> "" Then
tempcomment = Selection.Comment.Text
'End If
temprange2 = 1 'Declaring cell 1 for summary sheet

Do Until (temprange2 > 5) 'looping the no of times
Sheets("SUMMARY").Select
Range("E" & temprange2).Select
tempstr2 = Selection.Value
If tempstr2 = tempstr1 Then 'searching the cell to copy

Worksheets("SUMMARY").Range("E" & temprange2).AddComment tempcomment 'area when struck

Exit Do
Else
temprange2 = temprange2 + 1
End If
Loop

temprange1 = temprange1 + 1
Loop

End Sub

DavidCoutts · #2 (**permalink**) 11-30-04, 03:26

Hi InFuture

does this help at all, all ive done is tided your code a little and used the Pastespecial Comment Method, we could probably reduce this further down if you would like that

Code:

Sub Test()
Dim wksSht1 As Worksheet
Dim WksSummary As Worksheet

Dim tempstr1 As String 'variable for sheet 1
Dim tempstr2 As String 'variable for summary
Dim temprange1 As Integer 'cell no for sheet 1
Dim temprange2 As Integer 'cell no for summary

    Set wksSht1 = Worksheets("Sheet1")
    Set WksSummary = Worksheets("Summary")
    
    For temprange1 = 1 To 5 'Looping through the values to copy
        With wksSht1.Cells(temprange1, 2)
            tempstr1 = .Value 'pulling out the value of cell from sheet 1
            'If range("B" & temprange1).Comment <> "" Then
            .Copy 'Copy Contents of the cell
            'End If
        End With
        
        
        For temprange2 = 1 To 5 'looping the no of times
            With WksSummary.Cells(temprange2, 5)
                tempstr2 = .Value
                If tempstr2 = tempstr1 Then 'searching the cell to copy
                    'Paste the Comment Only
                    .PasteSpecial xlPasteComments
                    
                    Exit For
        
                End If
            End With
        Next temprange2
        
    Next temprange1

End Sub

HTH
Dave

infuture · #3 (**permalink**) 11-30-04, 05:04

Hi David,

Thanks for your prompt reply..you haff actually solve a great part of my problem.. Actually, my excel file has alot of worksheets about 50 worksheets and a summary page which summaries them altogether. i need to search within these 50 worksheets and in the cell column from A-Z to see whether the cell value is also present in the summary page. If it is, then i will paste the cell comment into to summary page. Is it possible to do that?

With regards,
James

DavidCoutts · #4 (**permalink**) 11-30-04, 05:33

Hi James

Ok Some questions before i start,

Can the value in the summary page be in any location or does it have to be in the identical cell etc,
can your values be located in any cell in columns A - Z of your worksheet,
What if there are multiple occurances of the same value what happens then,

Some quick pointers
to loop through every worksheet in your workbook

dim wks as Worksheet

For each wks in worksheets
if not wks.name = "Summary" then
'do something
end if
next wks

the same can be applied to ranges

it might be better when finding the cell in the summary sheet to use the find command

try notto select cells as this slows down your code, the same for activating worksheets

Give it a try and ill take a look at your code for you and help you sort it out

Dave

infuture · #5 (**permalink**) 12-03-04, 02:44

'Hi, thanks for your advice. I have edited the codes alittle so that it can
'loop through all the worksheets, but it seems to have problems when i try to
'loop through more rows and columns. Can i do a check first whether the cells
'has a comment before copying the contents. Please help..Thanks

Sub Macro1()

Dim WksSummary As Worksheet

Dim tempstr1 As String 'variable for sheet 1
Dim tempstr2 As String 'variable for summary
Dim temprange1 As Integer 'cell no for sheet 1
Dim temprange2 As Integer 'cell no for summary'
Dim colrange1 As Integer
Dim colrange2 As Integer
Dim wks As Worksheet

Set WksSummary = Worksheets("Summary")

For Each wks In Worksheets
If Not wks.Name = "SUMMARY" Then

For colrange1 = 2 To 7 ' (2 to 7) ' looping through the columns b4 the rows
For temprange1 = 1 To 500 '(1 to 500) 'Looping through the values to copy
With wks.Cells(temprange1, colrange1)
If .Comment <> "" Then 'checking for comments

tempstr1 = .Value 'pulling out the value of cell from sheet 1
'If range("B" & temprange1).Comment <> "" Then
.Copy 'Copy Contents of the cell
End If

End With

For colrange2 = 5 To 12 ' (5 to 12)
For temprange2 = 1 To 1434 '(1 to 1434)'looping the no of times
With WksSummary.Cells(temprange2, colrange2)
tempstr2 = .Value
If tempstr2 = tempstr1 Then 'searching the cell to copy
'Paste the Comment Only
.PasteSpecial xlPasteComments

Exit For

End If
End With
Next temprange2
Next colrange2

Next temprange1
Next colrange1
End If
Next wks
End Sub

infuture · #6 (**permalink**) 12-03-04, 02:48

Hi David,

There shouldn't be identical cells in the summary worksheet, and the cells's comments that is copied must be pasted on the summary that has the same cell content in it..Thanks for helping..

With regards,
James

infuture · #7 (**permalink**) 12-03-04, 02:54

Hi David,

There shouldn't be identical cells in the summary worksheet, and the cells's comments that is copied must be pasted on the summary that has the same cell content in it..Thanks for helping..

With regards,
James

DavidCoutts · #8 (**permalink**) 12-03-04, 04:34

Hi James

How about this

Code:

Sub Macro1()
Dim WksSummary As Worksheet
Dim wks As Worksheet
Dim RngFindComment As Range
Dim rngFindSummary As Range
Dim rngSummary As Range

Dim isect As Range
Dim oComment As Comment



Dim tempstr1 As String 'variable for sheet 1


    Set WksSummary = Worksheets("Summary")
    WksSummary.Activate
    Set rngSummary = Range(Cells(1, 5), Cells(1434, 12))
    
    'Loop Through Each Worksheet
    For Each wks In Worksheets
        If Not wks.Name = "SUMMARY" Then
            wks.Activate
            'Loop Through All the Comments on the Worksheet
            For Each oComment In wks.Comments
              'get the range of the comment
              Set RngFindComment = oComment.Parent
              'Check to see if it falls within the specfied Range of cells
              Set isect = Application.Intersect(Range(Cells(1, 2), Cells(500, 7)), Range(RngFindComment.Address))
              If Not isect Is Nothing Then
                  'Copy the Cells
                  RngFindComment.Copy
                  tempstr1 = RngFindComment.Value
                  WksSummary.Activate
                  'check for the correct range in the summary sheet and paste the comment
                  Set rngFindSummary = rngSummary.Find(tempstr1)
                  If Not rngFindSummary Is Nothing Then
                      rngFindSummary.PasteSpecial xlPasteComments
                  End If
              End If
            Next oComment
        End If
    Next wks

'Put the cell into the top Left hand Corner of worksheet summary
    WksSummary.Activate
    WksSummary.Range("A1").Select
End Sub

Dave

infuture · #9 (**permalink**) 12-03-04, 20:03

Thank you David.. you have solved my prob.! =)

Internet Services

Web Development

Digital Marketing

Consumer Tech