Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    193
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel PasteSpecial Formulas (Excel2K)

    Hi,

    I am puzzled to say the least with the following, assistance greatly appreciated!!

    I have the following Excel97 macro for pasting formulas:

    Sub PasteFormula()
    '
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    '
    End Sub

    This gives the following error in Excel2K:

    Run-time error '1004'
    PasteSpecial method of Range class failed

    When I record this macro in Excel2K I get the same code as below and it too fails!

    Sub MacroPS()
    '
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    '
    End Sub

    When I follow the VBA Help in Excel2K and code the following macro, it also fails with the same error!

    Sub PasteFormula()
    '
    Selection.PasteSpecial Paste:=xlPasteFormulas, _
    Operation:=xlPasteSpecialOperationNone, _
    SkipBlanks:=False, Transpose:=False
    '
    End Sub

    Can anyone suggest how I can get this code to work?

    Thanks in anticipation!

    Peter Moran

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PasteSpecial Formulas (Excel2K)

    I get that error if the selection contains more than one cell, but not the same number as the number of cells copied. It works for all other combinations I tried on XL2000. I do not have XL97 to try it on.

    What did you copy, and what is the selection?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    193
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel PasteSpecial Formulas (Excel2K)

    Legare,

    Thanks for your reply.

    I also no longer have XL97

    My efforts were all on XL2000 (SR-1) attempting to copy a single cell - D10 to a single cell - E10 with a formula as below:

    =SUM(D69)

    The simplest possible siruation!

    I also have had a look at MSDN for XL2000 using PasteSpecial, but there is only a known problem related to ColumnWidths.

    I was hoping to have a single button to do what currently takes 4 clicks via the Edit menu - Edit, PasteSpecial, Formulas, OK.

    I have not been able to get it to work - full stop!

    Regards and thanks again,

    Peter Moran

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,945
    Thanks
    0
    Thanked 203 Times in 184 Posts

    Re: Excel PasteSpecial Formulas (Excel2K)

    Hi Peter,

    The following code, which I use in XL2K and previously used in XL97 works for me:

    Sub PasteSpecialFormulas()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlFormulas
    End Sub

    I also attached it to a toolbar button and made it private (you needn't).

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PasteSpecial Formulas (Excel2K)

    Irritatingly, if you access a macro using alt-F8 or by choosing tools, macros, macro, the clipboard is cleared.

    Attach your macro to a toollbar button or a shortcut key and you should be fine.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    193
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel PasteSpecial Formulas (Excel2K)

    Hi,

    Thanks guys!

    Its now working like a charm!

    I now see that if you watch the cell as you select Tools Macro Macros the clipboard does appear to be lost - very devious of Microsoft!

    Regards,

    Peter Moran
    Two (or four!) heads are always better than one!!

Posting Permissions

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