+ Reply to Thread
Results 1 to 6 of 6

Macro to copy sheets from workbook and create new workbook

  1. #1
    Registered User
    Join Date
    11-04-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    29

    Macro to copy sheets from workbook and create new workbook

    Hello,

    Could use some help with the creating a macro that copies specific sheets (sheets are named) and then creates a new workbook and pastes those copied sheets in that workbook. Then the macro should save the new workbook. I've written the code as follows:

    Please Login or Register  to view this content.
    What my code does at the beginning is ask the user what month and year the data is related to. After which it goes about copying the specified sheets (VMS Methods/Export Flowrate/Well tuning Parameters) and SHOULD create a new workbook with these sheets in it. It then should save the workbook with the file name that includes the Month and Year. However, it crashes out with a subscript out of range error at following line:

    Please Login or Register  to view this content.
    I think this happens because the active workbook becomes the NEW workbook, which essentially doesn't have the other sheets so excel can't continue with the macro. I just don't know how to get around it. In addition, if I comment these out and just copy for the first sheete ("VMS Methods"), excel still asks me for to name the file when doing "Save As". If I specify a file name for save as, it saves the file and closes the workbook but the file has no extension!!

    Any help would be much appreciated!

    Thanks

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Macro to copy sheets from workbook and create new workbook

    First, Set a variable to the workbook wit the sheets you want to copy, then add that to your copy lines, like so:

    Please Login or Register  to view this content.
    Second, I think that having 3 .Copy lines will create three new workbooks. I'd think you'd want code more like:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    11-04-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    29

    Re: Macro to copy sheets from workbook and create new workbook

    I adjusted my code as shown below (changed the order of the sheets to be copied):

    Please Login or Register  to view this content.
    But get subscript out of range for line:
    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Macro to copy sheets from workbook and create new workbook

    I don't think you want the ".xlsm" in your Workbooks() name.

  5. #5
    Registered User
    Join Date
    11-04-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    29

    Re: Macro to copy sheets from workbook and create new workbook

    Got it to work! Slightly different method - put the sheets in an array and copied them over to new workbook. Thanks for the advice

    Please Login or Register  to view this content.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,621

    Re: Macro to copy sheets from workbook and create new workbook

    Try this for creating the new workbook with only the 3 named worksheets.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

+ 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] Macro to copy cells from multiple sheets in workbook to multiple sheets in other workbook
    By KeithMale in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-13-2013, 05:37 PM
  2. [SOLVED] copy all sheets from one workbook to the workbook running the macro
    By synergio in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-28-2012, 06:20 AM
  3. Automatically Copy sheets in one workbook to create sheets in a new workbook..
    By leebarratt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-13-2011, 03:14 AM
  4. Create new workbook, temporarily name it and copy sheets to it
    By DerbyJim in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-12-2006, 01:10 PM
  5. [SOLVED] create workbook, copy sheets
    By Pierre via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-25-2005, 03:05 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