VBA to Conditionally Reorder/Move Sheets Upon Workbook Open

[default_name]

Hey all,

I have a workbook with a tab named 'References'
Based on the current date, I would like the 'References' tab/worksheet to be assigned/moved as follows.

If today's date falls between:	'References' Sheet Position in the Workbook
Jan13 - Jan26	3rd tab from left
Jan27 - Feb9	4th tab from left
Feb10 - Feb23	5th tab from left
Feb24 - Mar8	6th tab from left

....it continues for several more sheets in the workbook (27 to be exact).
I think I can get the rest of the sheet set up as long as the initial pattern is set for the others.

I imagine it would be something like this integrated into a conditional statement?

Sheets("References").Move before:=Sheets(3)

Thanks in advance for your help!

 

[skillilea]

I'd probably move the sheet data to a table but something like this

Sub MoveReferencesTab()
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    Dim referencesSheet As Worksheet
    
    ' dates  (maybe read from a table)
    startDate = DateSerial(Year(Date), 1, 13)
    endDate = DateSerial(Year(Date), 1, 26)
    
    Set referencesSheet = ThisWorkbook.Sheets("References")
    
    ' Get the current date
    currentDate = Date
    
    ' again, I'd put this in a table with the sheet position and just loop it
    If currentDate >= startDate And currentDate <= endDate Then
        referencesSheet.Move Before:=ThisWorkbook.Sheets(3)
    End If
End Sub

 

[default_name]

I'd probably move the sheet data to a table but something like this

Sub MoveReferencesTab()
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    Dim referencesSheet As Worksheet
 
    ' dates  (maybe read from a table)
    startDate = DateSerial(Year(Date), 1, 13)
    endDate = DateSerial(Year(Date), 1, 26)
 
    Set referencesSheet = ThisWorkbook.Sheets("References")
 
    ' Get the current date
    currentDate = Date
 
    ' again, I'd put this in a table with the sheet position and just loop it
    If currentDate >= startDate And currentDate <= endDate Then
        referencesSheet.Move Before:=ThisWorkbook.Sheets(3)
    End If
End Sub

Thanks for your help and quick response!
How would you recommend I go about implementing the data into a table?
I am realizing that hard-coding this may end up being much more tedious than I initially anticipated.
Your code works for the first instance (Jan13-Jan26), but how would I implement others? Would I need multiple subs?

 

[skillilea]

Nope, not multiple subs. I would set up a table "tblSheetPosition" and have columns with startDate, endDate and sheetPosition.

Then in sub above, get the table, read it and loop through it to set the proper position of the references sheet based on the dates.

Make sense?

 

[default_name]

Nope, not multiple subs. I would set up a table "tblSheetPosition" and have columns with startDate, endDate and sheetPosition.

Then in sub above, get the table, read it and loop through it to set the proper position of the references sheet based on the dates.

Make sense?

I have gone in and created the table (see below).
How do I read in and loop through the table in the vba sub?

 

[skillilea]

I think this works. Haven't tested it. Make sure to set your sheet (codename) and set the table name.

Sub MoveReferencesTab()
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    Dim referencesSheet As Worksheet
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim sheetPosition As Integer
    Dim i As Long
    
    Set ws = shReference  ' your sheet name where the table is
    Set tbl = ws.ListObjects("tblSheetReference") ' set your name of table
    currentDate = Date
    Set referencesSheet = ThisWorkbook.Sheets("References")
    
    For i = 1 To tbl.DataBodyRange.Rows.Count
        startDate = tbl.DataBodyRange.Cells(i, 1)
        endDate = tbl.DataBodyRange.Cells(i, 2)
        sheetPosition = tbl.DataBodyRange.Cells(i, 3)

        If currentDate >= startDate And currentDate <= endDate Then
            referencesSheet.Move Before:=ThisWorkbook.Sheets(sheetPosition)
            Exit For
        End If
    Next
End Sub

 

[default_name]

I think this works. Haven't tested it. Make sure to set your sheet (codename) and set the table name.

Sub MoveReferencesTab()
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    Dim referencesSheet As Worksheet
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim sheetPosition As Integer
    Dim i As Long
  
[COLOR=rgb(184, 49, 47)]    Set ws = shReference  ' your sheet name where the table is[/COLOR]
    Set tbl = ws.ListObjects("tblSheetReference") ' set your name of table
    currentDate = Date
    Set referencesSheet = ThisWorkbook.Sheets("References")
  
    For i = 1 To tbl.DataBodyRange.Rows.Count
        startDate = tbl.DataBodyRange.Cells(i, 1)
        endDate = tbl.DataBodyRange.Cells(i, 2)
        sheetPosition = tbl.DataBodyRange.Cells(i, 3)

        If currentDate >= startDate And currentDate <= endDate Then
            referencesSheet.Move Before:=ThisWorkbook.Sheets(sheetPosition)
            Exit For
        End If
    Next
End Sub

Looks like the code is getting caught up at:

    Set ws = shReference  ' your sheet name where the table is

The table is on the sheet called References
I changed the code to:

    Set ws = "References"  ' your sheet name where the table is

...but it is still throwing an error

 

[skillilea]

I use the codename of the sheet which is set in properties.

You can use the sheet name this way:

Just change the code here:

    Set ws = ActiveWorkbook.Worksheets("SheetReference")  ' your sheet name where the table is
    Set tbl = ws.ListObjects("tblSheetReference") ' set your name of table
    currentDate = Date
    Set referencesSheet = ThisWorkbook.Worksheets("SheetReference")

 

[default_name]

That worked! Thank you so much for your patience!

Final code:

Sub MoveReferencesTab()
    Dim currentDate As Date
    Dim startDate As Date
    Dim endDate As Date
    Dim referencesSheet As Worksheet
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim sheetPosition As Integer
    Dim i As Long
  
    Set ws = ActiveWorkbook.Worksheets("SheetReference")  ' your sheet name where the table is
    Set tbl = ws.ListObjects("tblSheetPosition") ' set your name of table
    currentDate = Date
    Set referencesSheet = ThisWorkbook.Worksheets("SheetReference")
  
    For i = 1 To tbl.DataBodyRange.Rows.Count
        startDate = tbl.DataBodyRange.Cells(i, 1)
        endDate = tbl.DataBodyRange.Cells(i, 2)
        sheetPosition = tbl.DataBodyRange.Cells(i, 3)

        If currentDate >= startDate And currentDate <= endDate Then
            referencesSheet.Move Before:=ThisWorkbook.Sheets(sheetPosition)
            Exit For
        End If
    Next
End Sub

 

[skillilea]

Awesome, so glad. Let me know if you get stuck again.