Moving specific worksheets into a new workbook

[JackDanIce]

Hi,

Probably a simple answer to this but seem to be struggling thinking of!

I have a workbook with the following named worksheets:
Balance Sheet Filter
Original Data
Remaining
Filter1
Filter2
Static Data

The number of 'Filter' worksheets varies depending on the existing macro's output.

I wish to cut worksheets 'Filterx' and 'Remaining' into a new workbook but can't think how. Ideally, I want to build up a VBA line of code via a loop process as:

Worksheets(Array("Remaining","Filter1","Filter2" etc)).Select

Which I would then cut and insert into a new workbook.

Any suggestions on how I can do this please?

Thanks,
Jack

 

[Andrew Poulsom]

Try:

Sub Test()
    Dim ws As Worksheet
    With ThisWorkbook
        .Worksheets("Remaining").Select
        For Each ws In .Worksheets
            If ws.Name Like "Filter*" Then
                ws.Select False
            End If
        Next ws
        .Windows(1).SelectedSheets.Copy
        .Activate
        .Worksheets(1).Activate
    End With
End Sub

You can use Move instead of Copy if you want.

 

[JackDanIce]

Many thanks Andrew

 

[JackDanIce]

Hi Andrew,

Can you tell me what's wrong with the code I modified of yours please:

Sub OutputWorkbook()
Dim ws As Worksheet
ActiveSheet.Name = "Remaining"
With ThisWorkbook
    .Worksheets("Remaining").Select
    For Each ws In .Worksheets
        If ws.Name Like "Filter*" Then
            ws.Select False
        End If
    Next ws
    SelectedSheets.Move
End With
End Sub

It fails at the line coloured red. I'd like to move all the sheets into a new workbook, which the user can then save, discard, whatever they desire. Otherwise, thank you, yes it picks up the worksheets I want moving...

Thanks,
Jack

 

[Andrew Poulsom]

Try:

.Windows(1).SelectedSheets.Move