+ Reply to Thread
Results 1 to 6 of 6

Copy 4 sheets from template and dynamically name, Save template and leave workin file open

  1. #1
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    329

    Copy 4 sheets from template and dynamically name, Save template and leave workin file open

    I have a code that currently works, however it does not complete my task. Okay I have variables on Sheet5 "codes" (I prefer the code names to the sheet names... just in case someone renames them).

    sheet5 variables:
    C15 - Concatenation of new filename desired (c22 & I18)
    C22 - "base" file name
    I18 - date added to filename (added in format of mm.dd.yy)

    There are a total of 6 sheets in my template (where the macros are). The ones being copied are Sheet1, Sheet3, Sheet4, Sheet5. The desire is to copy those 4 sheets to a new workbook, name the file using the base name and the date, save as an xlsx (no macros) to the same directory that the template is located. I also want to have sheet1 and sheet5 hidden. Leave the NEW file open, save and close the template.

    Currently, the macro copies those 4 sheets to a new workbook, however I cannot get the template files to save and close, or the new file to hide those 2 sheets. I am close.

    Please Login or Register  to view this content.
    ~*~ Sherry ~*~
    Poinciana, FL

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,900

    Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

    Hi there,


    however I cannot get the template files to save and close, or the new file to hide those 2 sheets. I am close

    You were indeed!


    See if the following version of your code does what you need:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    329

    Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

    Greg M.

    Thanks for the response. It produced an error. Attached are screenshots. sheet1 & sheet5 are hidden, and sheet3 & sheet4 are displayed for the user. I am not sure what happened. Again, we get to the saving the file part when we have the errors.
    Attached Images Attached Images

  4. #4
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    329

    Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

    Greg M.

    I decided to try something, I moved my template from a development folder (for today) to my download folder... shorter path. I used your last code, and ran it a few times, most of the macro worked just fine. The macro:
    1) copied the 4 sheets
    2) hid the 2 sheets I wanted it to
    3) Renamed the new file based on the dynamic variables to the same path as the template
    4) The new file was left open (for the user to work)

    The only thing that did not happen is the original file (the template) needs to save and close after everything is done. Can ya help?

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,900

    Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

    Hi again,

    Thanks for your feedback.

    I think including the highlighted line at the very end of your routine should do what you need:

    Please Login or Register  to view this content.

    Hope this helps,

    Regards,

    Greg M

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,900

    Re: Copy 4 sheets from template and dynamically name, Save template and leave workin file

    Hi again,

    Many thanks for the Reputation increase - much appreciated!

    Glad I was able to help.

    Best regards,

    Greg M

+ 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] VBA to copy data to a template file then save it as a new file.
    By Efjoker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-20-2019, 12:10 PM
  2. [SOLVED] Open a template, make some changes to cells and then save the file
    By billj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-04-2019, 04:06 AM
  3. [SOLVED] looper to copy tabs of a data file into template and save as
    By amartin575 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-26-2014, 10:47 AM
  4. Replies: 3
    Last Post: 05-15-2013, 11:34 PM
  5. Creating dynamically sheets with data and template
    By Fulla in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2011, 02:40 PM
  6. Replies: 2
    Last Post: 05-20-2011, 07:26 AM
  7. [SOLVED] Open a template, copy 3 worksheets w ranges and save as workbook
    By gimme_this_gimme_that@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-03-2006, 01:35 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