Help with automatically updating tab names please :)

[Bex888]

Hi There,

I'm hoping someone may be able to assist.

I'm setting up a budgeting spreadsheet and want to align each tab with our pay cycle, each fortnight.

I would like each tab to be date named e.g. 02.01.2018, 16.02.2018 etc.

Is there a way I can automate this instead of updating 26 tabs manually?

Thanks!

 

[My Aswer Is This]

So a few questions:
1. I assume the dates are Day Month Year
First sheet to be named 02.01.2018 Second sheet named 16.02.2018

If we are making a new sheet for each of two weeks

I would think it would be 02.01.2018 then 16.01.2018


Do you want the sheets created or are the 26 sheets already created and you want them named?

 

[Logit]

.
This will rename/create tabs for the entire year. Each tab will be 14 days from the previous.

Enter the initial date in the format mm/dd/yyyy : 01/01/2019

The code will format the tab names to format dd/mm/yyyy

Option Explicit


Sub YearWorkbook2()
    Dim iWeek As Integer
    Dim sht As Variant
    Dim sTemp As String
    Dim dSDate As Date


    sTemp = InputBox("Date for the first worksheet:", "End of Week?")
    dSDate = CDate(sTemp)
    
    Application.ScreenUpdating = False
    Worksheets.Add After:=Worksheets(Worksheets.Count), _
      Count:=(26 - Worksheets.Count)
    For Each sht In Worksheets
        sht.Name = Format(dSDate, "dd.mm.yyyy")
        dSDate = dSDate + 14
    Next sht
    Application.ScreenUpdating = True
End Sub

 

[Bex888]

Hey There!

Thanks for your reply! Sorry, that was a typo on my part. Yes, the dates you have listed are the ones I am wanting to input, and so forth. I haven't created the sheets, it would be great to do this in one go, and I can paste my template in each. Thanks!

 

[Bex888]

.
This will rename/create tabs for the entire year. Each tab will be 14 days from the previous.

Enter the initial date in the format mm/dd/yyyy : 01/01/2019

The code will format the tab names to format dd/mm/yyyy

Option Explicit


Sub YearWorkbook2()
    Dim iWeek As Integer
    Dim sht As Variant
    Dim sTemp As String
    Dim dSDate As Date


    sTemp = InputBox("Date for the first worksheet:", "End of Week?")
    dSDate = CDate(sTemp)
    
    Application.ScreenUpdating = False
    Worksheets.Add After:=Worksheets(Worksheets.Count), _
      Count:=(26 - Worksheets.Count)
    For Each sht In Worksheets
        sht.Name = Format(dSDate, "dd.mm.yyyy")
        dSDate = dSDate + 14
    Next sht
    Application.ScreenUpdating = True
End Sub

Hi There,

Thanks for this, I tried copying and pasting the above code into VB however get a 'type error' mismatch? Any idea why? I tried entering the start date who hitting the 'play' macro button - as 02.01.2019 and 01.02.2019 (as you mentioned to do it this way above) but get an error both times. Do I need to enter the date in the code section also? Sorry - this is all new to me - codes and all!

 

[Bex888]

So a few questions:
1. I assume the dates are Day Month Year
First sheet to be named 02.01.2018 Second sheet named 16.02.2018

If we are making a new sheet for each of two weeks

I would think it would be 02.01.2018 then 16.01.2018


Do you want the sheets created or are the 26 sheets already created and you want them named?

Hey There!

Thanks for your reply! Sorry, that was a typo on my part. Yes, the dates you have listed are the ones I am wanting to input, and so forth. I haven't created the sheets, it would be great to do this in one go, and I can paste my template in each. Thanks!

 

[MARK858]

- as 02.01.2019 and 01.02.2019 (as you mentioned to do it this way above) but get an error both times!

That is not how Logit stated to enter the dates. Logit used hyphens not periods.

Enter the initial date in the format mm/dd/yyyy : 01/01/2019

 

[Bex888]

That is not how Logit stated to enter the dates. Logit used hyphens not periods.

Thank you!!! I was up all night with a teething daughter, clearly need some more sleep. This has worked. Thanks for you response, and picking me up on my error!

 

[My Aswer Is This]

You said then you would copy in your Template data into the sheets.

If that is what you plan to do why not let the script copy the Template sheet and make 26 copies naming them like you want?