+ Reply to Thread
Results 1 to 4 of 4

Converting formulas to values while copying/pasting columns between worksheets (vba)

  1. #1
    Forum Contributor
    Join Date
    02-05-2014
    Location
    Bay Area
    MS-Off Ver
    Excel 2010
    Posts
    164

    Converting formulas to values while copying/pasting columns between worksheets (vba)

    Good morning.
    I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete. I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.

    Please Login or Register  to view this content.
    Thank you for any help you can provide.

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Converting formulas to values while copying/pasting columns between worksheets (vba)

    As far as I can see, you have two issues:

    1. It looks that you copy single cell at a time, so you actually perform 2000*9 some 18000 copy paste operations

    2. You convert formulas in AI column one by one again so you have many formulas been recalculated with each copy/paste. If you have formulas in other columns, which you don't copy they are recalculated too. consider to temporary switch Calculation to manual.


    Post the full code and sample workbook for more assistance
    Last edited by buran; 02-06-2014 at 11:01 AM. Reason: typo
    If you are pleased with a member's answer then use the Star icon to rate it.

  3. #3
    Forum Contributor
    Join Date
    02-05-2014
    Location
    Bay Area
    MS-Off Ver
    Excel 2010
    Posts
    164

    Re: Converting formulas to values while copying/pasting columns between worksheets (vba)

    I'm surprised I only have two issues. However, the cell-by-cell copy/paste isn't surprising. My full code for this macro is attached. Thank you for thinking about, and hopefully responding to, my issue. I'm just diving into the VBA world and it's intimidating.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Converting formulas to values while copying/pasting columns between worksheets (vba)

    I must admit that given the macro and the fact that you have additional criteria regarding the values in column U there is little to be done to improve the macro without more understanding of the underlying data. I assume you have formulas not only in AI column on Data sheet and also you don't want to loose these formulas. Maybe someone else on the forum will give you ideas how to continue.

+ 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 Data from WorkSheets and Pasting them to a different Worksheets/Workbook
    By JRayhan in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-14-2013, 03:56 PM
  2. Copying Data from WorkSheets and Pasting them to a different Worksheets/Workbook
    By JRayhan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-06-2013, 11:22 AM
  3. Need help - Copying and pasting worksheets / formulas
    By memetics in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-01-2012, 01:55 AM
  4. Copying and pasting worksheets into new worksheet as values
    By MikeFranz123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-03-2012, 05:42 AM
  5. Converting/Pasting non-contigous ranges of formulas created by VBA to values
    By ron2k_1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-07-2011, 05: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