Move worksheets to new workbook without losing current activation

[KimberlyHeart]

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..

'   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!

 

[Jerry Sullivan]

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

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.

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.

 

[KimberlyHeart]

Thank you Jerry!

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

'   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.