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!

 

[smozgur]

That worked! Thank you so much for your patience!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[Peter_SSs]

Do you need a table? It looks like the change dates are each all a neat 14 days apart (after the first date). If that is the case, would this simple code do what you want without the need for any table or list?

Sub Move_References()
  Application.ScreenUpdating = False
  With Sheets("References")
    .Move After:=Sheets(Sheets.Count)
    .Move After:=Sheets(Int((Date - DateSerial(Year(Date), 1, 1) + 2) / 14) + 1)
  End With
  Application.ScreenUpdating = True
End Sub