Move worksheets to new workbook without losing current activation

KimberlyHeart

New Member
Joined
Mar 17, 2016
Messages
19
I have a workbook that I've been working on to automate a lot of activity. It currently has one large worksheet which is updated monthly. The data on this main worksheet includes data from various states, which I then sort and separate into individual worksheets based upon the state (I found code from here to do that!). The problem is that I now have 40-50 new worksheets in this original workbook. For example, I have a sheet named "AK", "AL", "AR", through "WY".

I found some code, which will select all of these new sheets, and then move them to a new workbook..

Code:
'   Move new sheets to new separate workbook
    Workbooks.Add
    Windows("Original.xlsm").Activate
    [highlight]Sheets(Array("AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "IA", "ID", _
                 "IL", "IN", "KS", "KY", "LA", "MA", "MD", "ME", "MI", "MN", "MO", "MS", "MT", _
                 "NC", "ND", "NE", "NH", "NJ", "NM", "NV", "NY", "OH", "OK", "OR", "PA", "RI", _
                 "SC", "SD", "TN", "TX", "UT", "VA", "VT", "WA", "WI", "WV", "WY")).Select[/highlight]
    Sheets("AK").Activate
    Sheets(Array("AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DE", "FL", "GA", "HI", "IA", "ID", _
                 "IL", "IN", "KS", "KY", "LA", "MA", "MD", "ME", "MI", "MN", "MO", "MS", "MT", _
                 "NC", "ND", "NE", "NH", "NJ", "NM", "NV", "NY", "OH", "OK", "OR", "PA", "RI", _
                 "SC", "SD", "TN", "TX", "UT", "VA", "VT", "WA", "WI", "WV", "WY")).Move Before:=Workbooks("Book1").Sheets(1)
    Windows("Original.xlsm").Activate

However... I keep getting a Run-time Error '9' "Subscript out of range" on the SELECT statement highlighted.

Could it be that I have it looking for all 50 states/sheets, when there is only 48 sheets available? If so, how do I fix that?

Also... while creating this code, I tried recording a macro and using portions of that code to help with this. In doing so, as it appears above, I have to move these new sheets into the newly created workbook, and then RE-ACTIVATE my original workbook. The name of this workbook may change, and I'm afraid this code will not work if the name of the "Original" changes. How do I avoid that?

Thanks!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi Kimberly,

Yes, you would get that Subscript out range error if one or more of the 50 sheets you list doesn't exist. If you will always have the same 48 sheets, you could modify the list of states in the code. If the number and names of those sheets varies, then you need a dynamic way to specify the sheets to be moved. For example, you could define the sheets to be moved as all the sheets after the 2nd sheet, or all the sheets in the workbook except "Summary" and "Raw Data".

Sheets can be moved to a new workbook without doing all the steps that the macro recorder saves. To move

This statement moves these 3 sheets to a new workbook without Workbook.Add, .Select, or .Activate

Code:
Sheets(Array("AK", "AL", "AR")).Move

Regarding returning to your original workbook without knowing it's name in advance there's a few ways to that.

If this macro is in that workbook, then the easiest method is to use the ThisWorkbook object to return to that workbook.

Code:
Sheets(Array("AK", "AL", "AR")).Move
'--return user to original workbook that holds the macro code
ThisWorkbook.Activate

If the macro is in a separate workbook than the one that has the original sheets, you can read and save a reference to the ActiveWorkbook before moving the sheets, then use that reference to return.
 
Last edited:

KimberlyHeart

New Member
Joined
Mar 17, 2016
Messages
19
Thank you Jerry! :)

I used this code to fix my problem with some states not showing up.

Code:
'   Move new sheets to new separate workbook
    For Each wb In ThisWorkbook.Worksheets
        If Len(wb.name) <= 2 Then
            If wa Is Nothing Then
                wb.Move
                Set wa = ActiveWorkbook
            Else
                wb.Move after:=wc
            End If
            Set wc = ActiveSheet
        End If
    Next wb

    ThisWorkbook.Activate

Works like a charm.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,393
Messages
5,601,403
Members
414,449
Latest member
Pashtun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top