+ Reply to Thread
Results 1 to 11 of 11

Copy Paste Values Except Formula which having "Sum" function.

  1. #1
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Copy Paste Values Except Formula which having "Sum" function.

    I wants to Copy Paste all the data of worksheets as Paste Special Values EXCEPT the cells which Having "=SUM" formula.

    Please help.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Copy Paste Values Except Formula which having "Sum" function.

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and click "manage attachments" to open the upload window.


    To add a file to a post

  3. #3
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Copy Paste Values Except Formula which having "Sum" function.

    Please refer the file attached.

    There are many formulas in the worksheets. I want everything except the cells which contains formulas which starting from "=SUM" on every worksheet to be paste as values. Thanks
    Attached Files Attached Files
    Last edited by AliGW; 03-29-2018 at 03:07 AM. Reason: Unnecessary quotation removed.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Copy Paste Values Except Formula which having "Sum" function.

    You will need VBA to do this: Copy/paste everything except lines containing "Total" in column C (?)

  5. #5
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Copy Paste Values Except Formula which having "Sum" function.

    Yes I need vba codes for this.

    Wherever the formula starts with " =Sum " it should remain as it is, and all the remaining formulas should be copy and paste special as values.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Copy Paste Values Except Formula which having "Sum" function.

    FYI:

    For "Grand Total" you could use ..

    =SUMIF($A:$A,"TOTAL",D:D)


    Copy across

  7. #7
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Copy Paste Values Except Formula which having "Sum" function.

    Thanks but this is not what I exactly wanted. I require all cells to be copy and pasted as values, i.e. to convert formulas in those cells to text or number. (Except those cells in which formulas starting with SUM function/s).

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Copy Paste Values Except Formula which having "Sum" function.

    YES .. I know: I was saying you could simplify the formula.

    You need to move this thread to VBA/Macro forum.

  9. #9
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Copy Paste Values Except Formula which having "Sum" function.

    How to move this thread to VBA/Macro forum? Do I need to re-post it?

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Copy Paste Values Except Formula which having "Sum" function.

    Ha! Nothing like replying to a thread 13 months later.

  11. #11
    Forum Contributor
    Join Date
    07-09-2013
    Location
    Nagpur, India
    MS-Off Ver
    Excel 2010 / 2013
    Posts
    293

    Re: Copy Paste Values Except Formula which having "Sum" function.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-22-2016, 09:21 AM
  2. Short "Basic" Macro to copy and paste formulas "N" times.
    By gradyhawks in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-09-2014, 02:34 PM
  3. [SOLVED] Keyboard shortcut for "copy paste special values?"
    By Star AJ in forum Excel General
    Replies: 4
    Last Post: 03-19-2014, 06:34 AM
  4. Replies: 4
    Last Post: 11-04-2013, 11:29 AM
  5. [SOLVED] Need to modify the Paste function of this VBA Macro from "Paste" to "Paste Special Values"
    By zicitron in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2013, 03:44 AM
  6. Anyone know a way to "Copy/paste Values" for conditional formatting?
    By jlax34 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-01-2012, 05:22 PM
  7. Replies: 1
    Last Post: 01-19-2011, 03:26 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1