Move Sheets Containing... ???

[DouglasWicker]

Morning All,

I have a workbook that consists of 1000+ sheets. They all have a weekday and a duplicate number as their sheet names. e.g Monday 12, Tuesday 15, Sunday 34 etc. THey are not in order of days. I'm trying to use vba to move all sheets containing "Monday" into a new workbook so will then Only have Mondays in that workbook.

I currently have this code but am getting debug errors on the ActiveSheet.Move line.

Sub MoveMondays()
Dim ws As Worksheet


    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "Monday*" Then
            ActiveSheet.Move Before:=Workbooks("Monday Test.xlsx").Sheets(1)
        End If
    
    Next ws
    
End Sub

 

[JackDanIce]

Try changing ActiveSheet.Move to ws.Move
If that doesn't work, what does the error say?

 

[DouglasWicker]

Try changing ActiveSheet.Move to ws.Move
If that doesn't work, what does the error say?

Tried that but still get Run-time error '9': Subscript out of range

 

[JackDanIce]

Try:

Sub MoveMondays()

    Dim ws      As Worksheet
    Dim wkb     As Workbook
    Dim strDest As String

    strDest = "Monday Test.xlsx"

    On Error GoTo ExitMe
        Set wkb = Workbooks(strDest)
    On Error GoTo 0
    
    If ActiveWorkbook.Worksheets.count = 1 Then
        MsgBox "Not enough worksheets to move", vbExclamation, "Invalid File"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    For Each ws In ActiveWorkbook.Worksheets
        If InStr(ws.Name, "Monday") Then ws.Move before:=wkb.Sheets(1)
        If ActiveWorkbook.Worksheets.count < 2 Then Exit For
    Next ws
    
    Application.ScreenUpdating = True
    Set wkb = Nothing
    Exit Sub
    
ExitMe:
    MsgBox Replace("Workbooks: @wkb does not exist", "@wkb", strDest), vbExclamation, "Workbook not found"
    
End Sub

 

[DouglasWicker]

Try

Unfortunately my excel just stops responding when I try and run that code :/

 

[DouglasWicker]

Try:

It works if i just use step into but wont when I try and run the code.