+ Reply to Thread
Results 1 to 5 of 5

Macro to Copy & Save a workbook multiple times

  1. #1
    Registered User
    Join Date
    01-23-2019
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    42

    Macro to Copy & Save a workbook multiple times

    Hi All,
    I need some help creating a Macro that can copy over an excel workbook multiple times. we have to copy & save this file over 1,000 times and its currently a manual process that takes more than 3 days to accomplish

    Here is what I am trying to accomplish

    1. The Macro will copy and save the attached workbook( All tabs included) By Company #, for each COE( Column G) & Dept #( Column
    Example: for Company # 10 ( Column J): The Macro will copy and save the following files
    10-740-100
    10-740-103
    10-741-101
    10-741- 102
    10-741-104
    10-742-105
    10-800-201
    10-800-202
    And then it will repeat the same for The next Company # 15, until all companies in column J have been created

    2. The Macro will Save each file as: Dept #- Dept Code- Company Code- Budget Yr. Example file name: 100- Blue-LO-2022
    The Macro will save each file in a specific folder, in a specific location: for example I want all files for Company 10 to be save in one folder named 10
    3. As the Macro creates the Company(Column J)- COE(Column G) Dept(Column e) combination-, the Orange colored cells in the summary tab should also change to reflect the combination

    I am also open to suggestions on how to restructure the Summary tab that makes it more efficient to create the macro

    Thank you all for your help

    Test Sheet-Macro.xlsx

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Macro to Copy & Save a workbook multiple times

    the code is very simple. you need to loop your data, capture it into a variable on every iteration, and use the saveas method to save that book as a new file. if i'm not mistaken, saving a single book by doing this will create ''x'' number of new books with the same content in it as the original book. and that's what you want. however, if you want to literally COPY everything, see this article:

    https://docs.microsoft.com/en-us/off...orkbook.saveas

    they are not copying anything in the code sample there, but rather just creating a new book and renaming it. that's only 50% of the work you want done.

  3. #3
    Registered User
    Join Date
    01-23-2019
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    42

    Re: Macro to Copy & Save a workbook multiple times

    Hi Thanks for your reply, I am a VBA Novice so i wont even big to know how to use the code in the attached link you sent

  4. #4
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Macro to Copy & Save a workbook multiple times

    if that's the case, see the attached book. you can certainly carry on from here I would think. wouldn't you?

    routine 1: loops your table data and saves the book as new ones, with one of your specs from your original post.
    routine 2: loops your table only to the point of reaching the number of desired books entered by the user. the rest is the same as above.

    the code given to you is written in the most patternized and simplistic manner possible to learn from. this is how all of the cheap software vendors in the world are able to sell software so easily. they write this stuff in a few short weeks and hire flashy and convincing people to sell it. LOL. They're not intelligent people whatsoever, and I would personally never buy anything from them, but corporate people are generally not intelligent.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by vba_php; 11-19-2020 at 05:23 AM.

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

    Re: Macro to Copy & Save a workbook multiple times

    partyrental
    Totally different method and this should be faster.
    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] Copy a workbook multiple times and name from list
    By Excelski in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-08-2019, 09:33 AM
  2. [SOLVED] Events running multiple times per worksheet on workbook save
    By JimBobW in forum Excel General
    Replies: 4
    Last Post: 01-21-2016, 09:04 AM
  3. [SOLVED] Macro that can save a workbook multiple times with changes in each one.
    By glide2131 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2013, 05:03 PM
  4. Macro that can save a worksheet multiple times
    By glide2131 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-15-2013, 08:00 PM
  5. MACRO - Paste value and then save file multiple times ---- please help
    By aetedford in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2013, 08:53 PM
  6. [SOLVED] copy Activesheet multiple times in same workbook, name from list
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-05-2012, 06:01 PM
  7. Need To save a spreadsheet multiple times through a macro
    By mibsaweiss in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2006, 10:09 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