+ Reply to Thread
Results 1 to 6 of 6

Copying and pasting a formula as values to new sheet?

  1. #1
    Registered User
    Join Date
    02-18-2015
    Location
    Flower Mound, TX
    MS-Off Ver
    2013
    Posts
    32

    Copying and pasting a formula as values to new sheet?

    Hi all,

    Is it possible to have VBA copy and paste a range of data that is using a formula to a new worksheet as values? Currently when I try doing this, I get #REF! errors in the column.

    This is some sample data I'm using that has the formula in the cell:

    Please Login or Register  to view this content.
    and then this code is grabbing it:

    Please Login or Register  to view this content.
    I'm sure there's a much better accomplishing the above, but this is what I have cobbled together thus far. Thanks for the help!

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Copying and pasting a formula as values to new sheet?

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    02-18-2015
    Location
    Flower Mound, TX
    MS-Off Ver
    2013
    Posts
    32

    Re: Copying and pasting a formula as values to new sheet?

    Quote Originally Posted by AlphaFrog View Post
    Please Login or Register  to view this content.
    Hi AlphaFrog, I think this is going to work! One question though, is there a way to dynamically pull column N's data? Sometimes there might be more or less that 150 rows. Sorry - should have mentioned that in my original post!

    Thanks again for your help! I really appreciate it!

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Copying and pasting a formula as values to new sheet?

    Quote Originally Posted by RedditGold View Post
    a way to dynamically pull column N's data?
    Too vague.

    I don't see anywhere in the code pulling column N's data.
    What would determine how many rows you want to pull?

  5. #5
    Registered User
    Join Date
    02-18-2015
    Location
    Flower Mound, TX
    MS-Off Ver
    2013
    Posts
    32

    Re: Copying and pasting a formula as values to new sheet?

    Quote Originally Posted by AlphaFrog View Post
    Too vague.

    I don't see anywhere in the code pulling column N's data.
    What would determine how many rows you want to pull?
    So, the FLOOR formula that I'm using, that range that's being selected:
    Please Login or Register  to view this content.
    This column is getting it's information from a parallel column (Column M). So basically, the data I'm getting from column N is being dragged down (auto filled) to however much data is in Column M. The length of data in column M varies so the range for N needs to be dependent on Column M's length.

    Let me know if this makes sense - Thanks!

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,596

    Re: Copying and pasting a formula as values to new sheet?

    Quote Originally Posted by RedditGold View Post
    So, the FLOOR formula that I'm using, that range that's being selected:
    Please Login or Register  to view this content.
    This column is getting it's information from a parallel column (Column M). So basically, the data I'm getting from column N is being dragged down (auto filled) to however much data is in Column M. The length of data in column M varies so the range for N needs to be dependent on Column M's length.

    Let me know if this makes sense - Thanks!
    That makes sense but except the .Find code is looking for "temp" in any cell. It's not going to find "temp" as a result of the floor formulas and it's not going to copy column N. I don't see how the two macros relate.

+ 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. Copying range from one sheet and pasting values in the last row of another.
    By sungen99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-13-2015, 11:02 AM
  2. Copying & pasting a current region to another sheet w/o actually copying.....
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2014, 09:08 PM
  3. Replies: 9
    Last Post: 05-13-2013, 05:43 AM
  4. [SOLVED] Macro for Finding Sheet Names, then Copying/Pasting Values
    By Joel.Nemec in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-09-2012, 01:28 PM
  5. Formula not working after copying and pasting it to another sheet
    By excelgirl250 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-07-2011, 11:16 AM
  6. Replies: 2
    Last Post: 10-29-2009, 12:51 PM
  7. copying & pasting multiple values from one sheet to another
    By phdog in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-04-2009, 03:05 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