+ Reply to Thread
Results 1 to 10 of 10

Copy only data in range and not formula

  1. #1
    Registered User
    Join Date
    02-21-2013
    Location
    Reading, England
    MS-Off Ver
    Excel 2010
    Posts
    51

    Copy only data in range and not formula

    Hi,

    I have a work sheet that has lots of data in and gets regular updates. I'm able to to copy data from 3 columns and paste into a different sheet via a simple macro but I'm having issues of selecting only the cells with data and not the "empty" cells with a formula. i.e. the empty cells with formulas will eventually get data when it is added, but until then I don't want to copy the data across.

    The below is what I'm using and had hoped the xlPasteValues would have been the fix but is copying "blank" cells in exchange of the original formula. Does Copy have any options or alernatives?

    Please Login or Register  to view this content.

    Thanks,
    Neil

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

    Re: Copy only data in range and not formula

    You may have to loop through the range,
    You can change <>0 to <>"" depending on what the formula result is.

    Please Login or Register  to view this content.

  3. #3
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,024

    Re: Copy only data in range and not formula

    @ davesexcel
    In this instance would one set to true again
    Please Login or Register  to view this content.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy only data in range and not formula

    Does this help any?

    Please Login or Register  to view this content.

  5. #5
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Copy only data in range and not formula

    You didn't say if the "problem cells" comprise entire rows. If they do, try this:
    Please Login or Register  to view this content.
    You're still moving all the data, but with direct assignment, empty strings ("") won't transfer, so you can use SpecialCells (xlBlanks) to clean up.
    Copy/PasteSpecial, on the other hand, DOES transfer empty strings, so (with respect) post #4 code won't work.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 05-15-2017 at 08:57 AM.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: Copy only data in range and not formula

    Quote Originally Posted by coopedup View Post
    I'm able to to copy data from 3 columns and paste into a different sheet via a simple macro but I'm having issues of selecting only the cells with data and not the "empty" cells with a formula. i.e. the empty cells with formulas will eventually get data when it is added, but until then I don't want to copy the data across.
    Try
    Please Login or Register  to view this content.

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

    Re: Copy only data in range and not formula

    Quote Originally Posted by sintek View Post
    @ davesexcel
    In this instance would one set to true again
    Please Login or Register  to view this content.
    You don't have to set it to true.

  8. #8
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Copy only data in range and not formula

    I'm pretty sure Excel doesn't copy non-contiguous areas in one go. IF you have to loop, read the entire range into a variant, then assign the values to the new sheet via the loop.
    Please Login or Register  to view this content.
    Last edited by leelnich; 05-15-2017 at 09:45 AM.

  9. #9
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,024

    Re: Copy only data in range and not formula

    @ davesexcel
    You don't have to set it to true.
    Thanks

  10. #10
    Registered User
    Join Date
    02-21-2013
    Location
    Reading, England
    MS-Off Ver
    Excel 2010
    Posts
    51

    Re: Copy only data in range and not formula

    Thanks for all the great replys.

    Some of the suggestions got me thinking. Not all of my source columns have formulas so on my original select I took one of those that I can use and then that become my search column for determing that last empty cell. This keeps the macro smaller and easier for others to manage (some of the suggestions also went way over my head but set me on a different route of thinking).


    Please Login or Register  to view this content.

+ 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. [SOLVED] Macro to copy both value and format, but not the formula of a range of data
    By kent97 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2016, 10:26 PM
  2. Copy data of range C4:C100 If range Q4:Q100 is greater than 0(zero)
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2014, 06:23 AM
  3. [SOLVED] Copy data with 4 cell interval and copy range of data like vlookup with structured output
    By Daydreams in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-23-2013, 04:08 AM
  4. Copy/Paste range of data excluding certain rows and copy to another workbook
    By HoerbigAdm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2012, 02:51 PM
  5. [SOLVED] Copy data from a range of cells into a blank range based on common cell
    By vanmeterkj in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-03-2012, 10:18 AM
  6. How to copy a formula into large data range
    By Ozwilly in forum Excel General
    Replies: 4
    Last Post: 08-26-2011, 07:54 AM
  7. Copy range of cell data to range of cells arranged by groups
    By Wayne Makeeff in forum Excel General
    Replies: 0
    Last Post: 01-27-2005, 12:17 AM

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