+ Reply to Thread
Results 1 to 5 of 5

Help with making a Rename/Paste as values VBA more efficient

  1. #1
    Registered User
    Join Date
    01-27-2021
    Location
    atlanta
    MS-Off Ver
    365 (Enterprise)
    Posts
    24

    Help with making a Rename/Paste as values VBA more efficient

    Hi guys, I have a report template that we update daily with raw data, and we email another copy of this report but only with a few tabs copied as values. There's a couple steps to it: Copy 5 tabs to a new workbook in the same folder, rename the file to include today's date, rename three tabs to include the current Month name like "Dec 2021 Summary", and closing all Excel instances so nothing is corrupted.

    The code works but it takes 4-5 minutes to run...and I don't know if it's because the code needs a lot of memory or it's just inefficient. Was wondering if someone can look at it. Thanks!

    A second note that's more of a question. This macro is stored in the report template so I have to open it to run. I've read that I can instead use a VBScript whether the file is open or not. How would I add another line of code and convert this to a script?

    Please Login or Register  to view this content.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Help with making a Rename/Paste as values VBA more efficient

    Try changing this

    Please Login or Register  to view this content.
    To this:
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-27-2021
    Location
    atlanta
    MS-Off Ver
    365 (Enterprise)
    Posts
    24

    Re: Help with making a Rename/Paste as values VBA more efficient

    Thanks TMS! I see a small difference but it's still slow, it may be my computer then. Appreciate the help still!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Help with making a Rename/Paste as values VBA more efficient

    You could try setting Application.Calculation = xlCalculationManual

    See: https://docs.microsoft.com/en-us/off....xlcalculation

  5. #5
    Registered User
    Join Date
    01-27-2021
    Location
    atlanta
    MS-Off Ver
    365 (Enterprise)
    Posts
    24

    Re: Help with making a Rename/Paste as values VBA more efficient

    Meant to reply back with a thanks. It works great now!

+ 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] most efficient wat to QUICKLY paste values to another book.
    By moonbreakker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-02-2016, 04:40 PM
  2. Copy paste and rename folder based on Cell values
    By BrettRogersUK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-28-2014, 04:54 AM
  3. [SOLVED] Making Copy and Paste with .end offset more efficient
    By Ragnarok_421 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-23-2014, 02:06 PM
  4. Make Copy/Paste Values Macro more efficient when calculating
    By Phily915 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-22-2014, 04:28 PM
  5. [SOLVED] What is the most efficient way to copy, paste and transpose values between worksheets?
    By fredrs05 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2013, 03:51 PM
  6. More efficient script for insert new worksheets, copy and paste values?
    By SDBoca in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2011, 11:07 AM
  7. Making Copy and Paste Code more efficient
    By T De Villiers in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-18-2006, 11:31 AM

Tags for this Thread

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