Select a specific amount of sheets in a workbook with a code

[jordan11221992]

Hello. I have a work book with a sheet for every day of my companies 12 periods of the year by using
Sub MakeYear()
Dim SH As Worksheet
Dim D As Date, Y As Long
Set SH = ActiveSheet
Y = Val(InputBox("Year:"))
If Y < 2000 Then Exit Sub
If Y > 2100 Then Exit Sub
Application.ScreenUpdating = False
For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
Application.StatusBar = D
SH.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Range("A1").Value = D
ActiveSheet.Name = Format(D, "mmm dd")
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub

Is it possible to select certain sheets (12.28.2020 - 1.31.2021) and move them to a new work book without selecting all the sheets or moving one by one?

 

[StephenCrump]

If your selected sheets are consecutive, you could do something like this:

Sub MoveConsecutiveSheets()

    Dim First As Long, Last As Long
   
    First = Sheets("12.28.2020").Index
    Last = Sheets("1.31.2021").Index
   
    Sheets(Evaluate("Transpose(Row(" & First & ":" & Last & "))")).Move

End Sub

But won't you make life very difficult for yourself if every day is on a separate sheet? Presumably you'll be wanting to produce weekly or monthly summaries at some point?

 

[jordan11221992]

If your selected sheets are consecutive, you could do something like this:

Sub MoveConsecutiveSheets()

    Dim First As Long, Last As Long
  
    First = Sheets("12.28.2020").Index
    Last = Sheets("1.31.2021").Index
  
    Sheets(Evaluate("Transpose(Row(" & First & ":" & Last & "))")).Move

End Sub

But won't you make life very difficult for yourself if every day is on a separate sheet? Presumably you'll be wanting to produce weekly or monthly summaries at some point?

The work book is a simple communication log. So each day of the period is a new sheet that we review the next day and initial. Id rather have my sheets all set to go with the correct dates rather than having to delete contents of each sheet every day. My boss just renamed last years com logs by changing the year.. Leaving the dates and amount of days in this years periods wrong and now i have to edit them every day before entering my info. So todays for example without editing is dated 1/4/2020 and filled out with info and initialed by people from a year ago..

 

[jordan11221992]

The work book is a simple communication log. So each day of the period is a new sheet that we review the next day and initial. Id rather have my sheets all set to go with the correct dates rather than having to delete contents of each sheet every day. My boss just renamed last years com logs by changing the year.. Leaving the dates and amount of days in this years periods wrong and now i have to edit them every day before entering my info. So todays for example without editing is dated 1/4/2020 and filled out with info and initialed by people from a year ago..

When entering this code do i apply it to the entire work book?

 

[StephenCrump]

When entering this code do i apply it to the entire work book?

Sorry, I don't understand the question.

If you have a workbook with consecutive sheets named "Sheet1", "Sheet2", "Sheet3" ... "Sheet10", and you run this code, say:

Sub MoveConsecutiveSheets()

    Dim First As Long, Last As Long
  
    First = Sheets("Sheet2").Index
    Last = Sheets("Sheet9").Index
  
    Sheets(Evaluate("Transpose(Row(" & First & ":" & Last & "))")).Move

End Sub

then Sheet2:Sheet9 will be moved to a new workbook, leaving behind Sheet1 and Sheet10.

This is what I thought you wanted, based on your original post?

 

[jordan11221992]

Sorry, I don't understand the question.

If you have a workbook with consecutive sheets named "Sheet1", "Sheet2", "Sheet3" ... "Sheet10", and you run this code, say:

Sub MoveConsecutiveSheets()

    Dim First As Long, Last As Long
 
    First = Sheets("Sheet2").Index
    Last = Sheets("Sheet9").Index
 
    Sheets(Evaluate("Transpose(Row(" & First & ":" & Last & "))")).Move

End Sub

then Sheet2:Sheet9 will be moved to a new workbook, leaving behind Sheet1 and Sheet10.

This is what I thought you wanted, based on your original post?

This would work I believe.. So I could have maybe a "master copy" of period 1 for sheet 1 and a "master copy" of the next period for sheet 10 if it is going to leave those sheets behind? Eventually leaving me a work book with master copies for each period? Or is it possible to include sheets 1 and 10 as well?

 

[StephenCrump]

If you want to keep an unchanged master copy, you could use Sheets(...).Copy, rather than Sheets(...).Move.

It would be very simple, for example, to create a VBA loop that copied from your master workbook for the year:
- All November 2020 sheets into a Workbook called Nov20.xlsx,
- All December 2020 sheets into a Workbook called Dec20.xlsx,
- etc

The code could also easily accommodate different accounting periods, e.g. it looks like your January period covers the five weeks Mon 28 Dec to Sun 31 Jan?

If you'd like further help, perhaps you could provide more detail on what you'd like to do?

 

[jordan11221992]

If you want to keep an unchanged master copy, you could use Sheets(...).Copy, rather than Sheets(...).Move.

It would be very simple, for example, to create a VBA loop that copied from your master workbook for the year:
- All November 2020 sheets into a Workbook called Nov20.xlsx,
- All December 2020 sheets into a Workbook called Dec20.xlsx,
- etc

The code could also easily accommodate different accounting periods, e.g. it looks like your January period covers the five weeks Mon 28 Dec to Sun 31 Jan?

If you'd like further help, perhaps you could provide more detail on what you'd like to do?

Yes my period 1 is 5 weeks, mon 28 dec to sun 31 jan. So i would need 12 "periods" rather than actual months. So yes I need "period 1" in one work book then period 2 (2/1/2021-2/28/2021) in the next then period 3 (3/1/2021-4/4/2021) in another work book and so on.

 

[jordan11221992]

Sorry, I don't understand the question.

If you have a workbook with consecutive sheets named "Sheet1", "Sheet2", "Sheet3" ... "Sheet10", and you run this code, say:

Sub MoveConsecutiveSheets()

    Dim First As Long, Last As Long
 
    First = Sheets("Sheet2").Index
    Last = Sheets("Sheet9").Index
 
    Sheets(Evaluate("Transpose(Row(" & First & ":" & Last & "))")).Move

End Sub

then Sheet2:Sheet9 will be moved to a new workbook, leaving behind Sheet1 and Sheet10.

This is what I thought you wanted, based on your original post?

Or could i put
First=sheets("sheet1")
Last=sheets("sheet10")
To move those 1-10 days(sheets) into a seperate work book?

 

[StephenCrump]

Yes my period 1 is 5 weeks, mon 28 dec to sun 31 jan. So i would need 12 "periods" rather than actual months. So yes I need "period 1" in one work book then period 2 (2/1/2021-2/28/2021) in the next then period 3 (3/1/2021-4/4/2021) in another work book and so on.

Something like this perhaps:

Sub CreatePeriodWorkbooks()

    Dim PeriodsEnd As Variant
    Dim i As Long
    
    PeriodsEnd = Array("Dec 27", "Jan 31", "Feb 28", "Apr 04")
    
    Application.ScreenUpdating = False
    
    For i = UBound(PeriodsEnd) To LBound(PeriodsEnd) + 1 Step -1
        Sheets(Evaluate("Transpose(Row(" & Sheets(PeriodsEnd(i - 1)).Index + 1 & ":" & Sheets(PeriodsEnd(i)).Index & "))")).Move
        With ActiveWorkbook
            .SaveAs Filename:=ThisWorkbook.Path & "\" & "Period " & i & ".xlsx"
            .Close
        End With
    Next i
    
    Application.ScreenUpdating = True
    MsgBox "Finished!"
    
End Sub

I have changed my sheet names to match your formatting, i.e. MMM DD.

I have hard-coded Array("Dec 27", "Jan 31", "Feb 28", "Apr 04") because I don't know what date algorithm you're using, e.g. to determine why Period 1 starts on Monday 28 Dec rather than Mon 4 Jan? If we know the algorithm, we can easily generate the Period dates based on adding either 28 or 35 days to the previous Period end date.