+ Reply to Thread
Results 1 to 7 of 7

Excel VBA Export Certain Worksheets as New Workbook - Improvement

  1. #1
    Forum Contributor
    Join Date
    07-07-2014
    Location
    ericbartha.com
    MS-Off Ver
    2016 Professional
    Posts
    126

    Excel VBA Export Certain Worksheets as New Workbook - Improvement

    Good morning (or afternoon/night) everyone,

    I am newer to VBA (only been working with it for about 3 months) and ran into a new project at work. I had someone request the ability to export the data driving the dashboard. The below code is what I came up with. I took a small amount of inspiration from a post on Mr Excel, listed in the comments.

    My question to all of you: would you look through this and tell me ways I can optimize the code? I am looking for suggestions and comments on ways to imporve!

    Thanks to all who help,

    Eric Bartha

    EDIT: Just to be clear, the code as is works perfectly! I am just looking for ways to better do things! Thanks!

    Please Login or Register  to view this content.

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

    Re: Excel VBA Export Certain Worksheets as New Workbook - Improvement

    This will copy the 3 worksheets to a new workbook.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    07-07-2014
    Location
    ericbartha.com
    MS-Off Ver
    2016 Professional
    Posts
    126

    Re: Excel VBA Export Certain Worksheets as New Workbook - Improvement

    Thanks Norie! I've never worked with Arrays before in VBA, or any other language for that matter.

    I thought with Arrays you had to refrence the index number of the item? Or does that not apply since you are explicitly saying copy each of these?

    Br,

    Eric

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

    Re: Excel VBA Export Certain Worksheets as New Workbook - Improvement

    Using an array here is just a way to refer to multiple worksheets.

  5. #5
    Forum Contributor
    Join Date
    07-07-2014
    Location
    ericbartha.com
    MS-Off Ver
    2016 Professional
    Posts
    126

    Re: Excel VBA Export Certain Worksheets as New Workbook - Improvement

    Oh - this is brilliant! It eliminates the need of Workbook.Add and having to Delete the default sheets. Thanks very much Norie!

    Anyone else have another awesome suggestion?

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

    Re: Excel VBA Export Certain Worksheets as New Workbook - Improvement

    Eric

    As far as I can see there's not much else that could be done to optimize the code.

    Might be wrong though, perhaps someone else can suggest an improvement.

  7. #7
    Forum Contributor
    Join Date
    07-07-2014
    Location
    ericbartha.com
    MS-Off Ver
    2016 Professional
    Posts
    126

    Re: Excel VBA Export Certain Worksheets as New Workbook - Improvement

    Norie,

    That is much appreciated! I guess I am learning well! haha

+ 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. Replies: 7
    Last Post: 08-04-2014, 04:55 AM
  2. Export Worksheets from Source Workbook into a new Workbook
    By dhaval3547 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-02-2013, 06:16 AM
  3. Export Worksheets from Source Workbook into a new Workbook
    By dhaval3547 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2012, 09:04 AM
  4. Export two worksheets from a workbook and save with a name
    By thylander in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-18-2011, 11:50 AM
  5. Export worksheets from a workbook to the current workbook location path
    By legendkiller420 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2010, 04:08 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