Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    15

    Question Unanswered: copy comment from 1 worksheet and paste it on another.

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  3. #3
    Join Date
    Nov 2004
    Posts
    15

    THanks

    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

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    15

    Please help

    '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

  6. #6
    Join Date
    Nov 2004
    Posts
    15

    Thanks

    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

  7. #7
    Join Date
    Nov 2004
    Posts
    15

    Thanks

    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

  8. #8
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  9. #9
    Join Date
    Nov 2004
    Posts
    15

    Thanks

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •