+ Reply to Thread
Results 1 to 6 of 6

Export each selected sheet to new workbook via pastespecial and save

  1. #1
    Registered User
    Join Date
    08-26-2011
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Question Export each selected sheet to new workbook via pastespecial and save

    Hey everyone,

    I have a workbook with 5 sheets; 3 of which I need to export to separate workbook while maintaining number formats and values (pastespecial).

    Then I need to save each of these 3 new workbooks to the same directory as the original.

    Also would like to be able to customize the format of my saved data (will likely be using tab delimited, or csv).

    I've tried various code on the internet, but nothing allows me to choose which sheets i would like to use AND use the pastespecial format. Unfortunately there's no workbook object for pastespecial.

    Any insight is much appreciated!!

  2. #2
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Export each selected sheet to new workbook via pastespecial and save

    jason117
    If you want Excel spreadsheet 'copies' then consider saving a copy of the original workbook (3 times) and in each one delete the sheets that you don't need?
    No copy and paste needed.
    Barry

  3. #3
    Registered User
    Join Date
    08-26-2011
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Export each selected sheet to new workbook via pastespecial and save

    hey barry,

    I'm going to need to do this through vba, because I need to export each of those three sheets to a separate csv many times during the day. Also the sheets are linked with formulas so I need to copy only the values and formats.

  4. #4
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Export each selected sheet to new workbook via pastespecial and save

    jason117
    Yes - you can do this with VBA.
    However if it is text files you are needing to export you will not be able to embed formatting.
    Exporting data isn't the same as copying and pasting - this may be causing some confusion.
    Why not post up a workbook and re-explain your requirement with 'before' and 'after' examples.
    Barry

  5. #5
    Registered User
    Join Date
    08-26-2011
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Export each selected sheet to new workbook via pastespecial and save

    Hi again,

    Thanks for the response. I'd love to post the workbook, but it goes against company policy...i'm going to post the code of something that works quite well but not EXACTLY what I need, and maybe you could understand. If not, I'll edit my workbook out and pm it to you.


    Please Login or Register  to view this content.
    The problem with this code is that 1) I get #name errors because on my original workbook I use formulas (and custom functions) 2) I don't know where to place the code to save to tab delimited format.

    I think problem 1) can be solved by switching the ws.copy method to something that uses .pastespecial and 2) can be solved rather easily; however, my limited vba skills are struggling.

  6. #6
    Registered User
    Join Date
    08-26-2011
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Export each selected sheet to new workbook via pastespecial and save

    Sorry for the double post, but I solved everything out. I do have one problem, however. The cells in the original workbook are replaced with their values and their formulas have disappeared (EX: instead of concatenate(A1,A2) I just have "John Smith" in the original).

    Any way around that?



    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)

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