Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    PasteSpecialComments (XL 2002 SP3)

    The following is an extract of some code:-

    Range("cfoweingfcstother").Copy
    Range("cfoweingfcstnext").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteComments


    The code is designed to move a whole range of cells to the left as part of a Month End Rollover.


    Both Range names are valid. The third row executes without error. The fourth row of the above code generates the error "PasteSpecial Method of Range Class has failed"


    Anyone have any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecialComments (XL 2002 SP3)

    Your code (including the 4th line) runs without problems in a quick test I whipped up. Can you see anything special about the source range?
    Have you tried whether the code works with explicit range addresses (such as A130)?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: PasteSpecialComments (XL 2002 SP3)

    Hi Hans Long time etc....
    Source Range on this occasion has no comments, but can have in the future. I have always thought that using explicit range addresses was bad coding. Will try and see.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: PasteSpecialComments (XL 2002 SP3)

    Like hans I can not replicate the error. I have has a soure with no comments, all comments or some comments. I also tried with the desitination have all, some, or no comments and all seem to work as expected. i even tried variations with different sizes of the range and could not get an error...

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: PasteSpecialComments (XL 2002 SP3)

    Using explicit range addresses was just meant as a test, to see if it matters.

    Would it be possible to post a small sample workbook with dummy data that demonstrates the error?

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: PasteSpecialComments (XL 2002 SP3)

    Hi Devious,

    Give the following code a tryout and see if it achieves your goal. Note that no selection or copy/paste operations are involved.

    The code also clears out any existing comments in the target range, on the presumption that you might be overwriting data (possibly commented) that were already there, plus it clears out the "cfoweingfcstnext" range afterwards, leaving it ready to receive new input, including comments.

    Sub MoveData()
    Dim i As Long
    Dim j As Long
    Dim Source As String
    Dim Target As String
    With ActiveSheet
    'Loop through all rows in the "cfoweingfcstnext" range
    For i = 1 To Range("cfoweingfcstnext").Rows.Count
    'loop through all columns in the "cfoweingfcstnext" range
    For j = 1 To Range("cfoweingfcstnext").Columns.Count
    'Get the corresponding cell in the "cfoweingfcstother" range
    Source = .Range(Range("cfoweingfcstnext").Cells(i, j), Range("cfoweingfcstnext").Cells(i, j)).Address
    Target = .Range(Range("cfoweingfcstother").Cells(i, j), Range("cfoweingfcstother").Cells(i, j)).Address
    With Range(Target)
    'copy the values across
    .Value = Range(Source).Value
    'delete any existing comment
    If Not .Comment Is Nothing Then .Comment.Delete
    'test the corresponding 'source'cell for a comment and replicate if found
    If Not Range(Source).Comment Is Nothing Then
    .AddComment
    .Comment.Text Text:=Range(Source).Comment.Text
    End If
    End With
    Next j
    Next i
    With Range("cfoweingfcstnext")
    .ClearContents
    .ClearComments
    End With
    End With
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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