+ Reply to Thread
Results 1 to 8 of 8

Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

  1. #1
    Registered User
    Join Date
    04-28-2021
    Location
    Pennsylvania
    MS-Off Ver
    365
    Posts
    4

    Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    Hello, I have a workbook with multiple worksheets that I want to export. I want to select the worksheets, run the macro, have selected sheets exported, load original theme, copy original column widths, and then break links and delete temporary files created. The code works if I only select one worksheet. Selecting more than one sheet leads to the, "you can't paste this here because the copy area and paste area aren't the same size" error. Any ideas?

    Please Login or Register  to view this content.
    Last edited by gpops; 05-13-2021 at 10:43 AM.

  2. #2
    Forum Contributor
    Join Date
    05-09-2021
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    148

    Re: Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    Hello gpops,

    There is something in the handling of the Activewindow vs activeWorkbook that does not seem to fit...

    This code is oviously in the source document, the document from where you want to copy worksheets, as expected. So the active workbook here is the source Workbook...
    Sub CopySelectedSheetsToNewWorkbooks()
    This theory is reinforced here, you want to copy each sheet in the source doc
    For Each SHT In ActiveWindow.SelectedSheets
    Now here you are jumping into saying that the name of the active workbook is actually the NewWB... this strange... unless you want to have the exact same name on two files, which I assume that you dont, since Excel cannot have two filess with same name open at the same time..
    NewWB = ActiveWorkbook.Name
    Then you want to activate this NewWB, but I dont see it being created...
    Workbooks(NewWB).Activate
    Maybe I am reading something wrong but it seems to me that you need to make clear objects referring to the different workbooks and avoid as much as possible the "Active". This way you dont have to activate, because you know what you are working with.

    Additionally to this, you are copying both Sheet and Cells before pasting anything.... this cannot work as expected...

    I havent tested the below, but hopefully it gives you an idea of what I mean...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-28-2021
    Location
    Pennsylvania
    MS-Off Ver
    365
    Posts
    4

    Re: Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    Thanks for taking the time to reply.

    Additionally to this, you are copying both Sheet and Cells before pasting anything.... this cannot work as expected...
    If you don't specify either Before or After, Microsoft Excel creates a new workbook that contains the copied Worksheet object. The newly created workbook holds the Application.ActiveWorkbook property and contains a single worksheet.
    That is from the Microsoft docs on Worksheet.Copy. When I use Worksheet.Add, I need to delete the blank Sheet1. I think it is cleaner copying the sheet to a new workbook.

    I like your suggestion of using clear objects and have been playing with it, but still cannot get it to work... Same "you can't paste this here because the copy area and paste area aren't the same size" error.

    If you make a blank workbook with three worksheets, select one worksheet and run the following macro, it works. Selecting more than one worksheet leads to the error. It's driving me crazy

    Please Login or Register  to view this content.
    Last edited by gpops; 05-14-2021 at 04:21 PM.

  4. #4
    Registered User
    Join Date
    04-28-2021
    Location
    Pennsylvania
    MS-Off Ver
    365
    Posts
    4

    Re: Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    Still unsure why this is not working. I've been trying different ways and can't get it to function.

  5. #5
    Forum Contributor
    Join Date
    05-09-2021
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    148

    Re: Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    Hello gpops,

    Do you want the sheets to be exported to the same workbook? Or do you want a separate workbook for each sheet?

  6. #6
    Forum Contributor
    Join Date
    05-09-2021
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    148

    Re: Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    Your problem is here:
    Please Login or Register  to view this content.
    Your "for each" relies on the ActiveWindow, while you acknowledge that the active window changes... you use it to define the destination workbook.

    Change to this:
    Please Login or Register  to view this content.
    Testing your code it breaks on the
    Please Login or Register  to view this content.
    But since the complete sheet is copied it is not needed.

    This Create a new workbook for each sheet. I assume this is what you want.

    Hope that works for you!

  7. #7
    Registered User
    Join Date
    04-28-2021
    Location
    Pennsylvania
    MS-Off Ver
    365
    Posts
    4

    Re: Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    Thank you for all of your help. The column widths is what is bothering me. I went about this another way, using your earlier suggestion of Workbooks.Add and got it to function correctly!

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    05-09-2021
    Location
    Sweden
    MS-Off Ver
    Office 365
    Posts
    148

    Re: Copy Selected Sheets to New Workbook - Not Working with Multiple Sheets Selected

    HI,

    Strange that you had issues with the column widths, when I used the code I provided, the complete sheet was copied, as is, with colors, widths and everything.

    But good that you found a solution. Using the Workbooks.add is a good way to solve this.

+ 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 selected sheets based on filtered cell values to new workbook
    By Galdranxsl in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-05-2020, 08:58 AM
  2. Replies: 3
    Last Post: 11-18-2019, 09:32 AM
  3. Replies: 2
    Last Post: 05-06-2014, 08:26 AM
  4. [SOLVED] Copy selected files sheets to the this workbook
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2013, 02:21 PM
  5. Replies: 6
    Last Post: 03-29-2006, 07:50 AM
  6. [SOLVED] How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook?
    By Dmitry in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2006, 07:50 AM
  7. Replies: 6
    Last Post: 03-29-2006, 07:50 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