+ Reply to Thread
Results 1 to 4 of 4

Copy a sheet, rename it by value in range then export product to new workbook and rename

  1. #1
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work 2013 Home 2019
    Posts
    348

    Question Copy a sheet, rename it by value in range then export product to new workbook and rename

    Hi Guys,

    I have a workbook that, thanks to the enormous help on here, does all kinds of wonderful things.

    To finish it off, what I'd like to do is this

    Copy the tab called "User to System Matrix" but have all the contents saved as values
    Rename the new tab by the value in cell C2 of the "Welcome" tab

    Export the new tab into a new workbook and save it with the filename of today ("yyyymmdd - Tabname")
    Save the original workbook with the filename of "yyyymmdd - <value in cell C2 of the "Welcome" tab> - MASTER

    Sorry, on both the Saves, can it put the filename but not actually save but allow the user to navigate to where they wish to save the files

    Thanks guys

    Terry
    Last edited by MagicMan; 03-30-2015 at 01:39 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-12-2014
    Location
    Nottingham
    MS-Off Ver
    2013
    Posts
    383

    Re: Copy a sheet, rename it by value in range then export product to new workbook and rena

    This Code will do all of the exporting etc but not save the files
    Please Login or Register  to view this content.
    This is the Save As command you can use to help you..
    It is possible to automatically save to a folder using a path but I am unsure if you can leave the dialog box open!
    https://msdn.microsoft.com/en-us/lib.../ff195820.aspx
    Last edited by ARowbot; 03-30-2015 at 02:36 PM.

  3. #3
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,709

    Re: Copy a sheet, rename it by value in range then export product to new workbook and rena

    user to navigate to where they wish to save the files
    Try adding the command

    Please Login or Register  to view this content.
    at the end at the code.

    If e is the string that contains the new file name include it in the "Application" as

    Please Login or Register  to view this content.
    Alf
    Last edited by Alf; 03-30-2015 at 05:16 PM. Reason: correcting command

  4. #4
    Forum Contributor
    Join Date
    03-08-2011
    Location
    London
    MS-Off Ver
    Work 2013 Home 2019
    Posts
    348

    Re: Copy a sheet, rename it by value in range then export product to new workbook and rena

    Thanks for your help guys. You pointed me in the right direction.

    I finally resolved it by exporting a hidden sheet to act as a control sheet. This control sheet contained the data to create the filename

    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. Macro to copy the active sheet to new workbook and rename
    By shansakhi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-06-2014, 01:27 AM
  2. Replies: 0
    Last Post: 07-24-2014, 09:11 AM
  3. Replies: 0
    Last Post: 06-28-2014, 02:26 PM
  4. Copy and rename a protected sheet into the same workbook
    By B0wser in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2014, 04:02 PM
  5. Copy and rename a sheet in an existing workbook?
    By robertse in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-09-2010, 02:00 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