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. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #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

  4. #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

  5. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,919
    Thanks
    0
    Thanked 194 Times in 177 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]

  6. #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

  7. #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
  •