How can I create worksheet as date from 1 Jan 15 to 31 Dec 15

Closed
rungjungz
Posts
1
Registration date
Saturday January 17, 2015
Status
Member
Last seen
January 17, 2015
- Jan 17, 2015 at 09:06 PM
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
- Jan 19, 2015 at 04:30 AM
Hello,
I would like to create 365 worksheets with sheet name as date.
Could anyone help me.
Many thanks in advance.

:))

1 reply

vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Jan 19, 2015 at 04:30 AM
Hello Rungjungz,

This code could help you:-

Sub DateFill()
 
  Dim S As Integer
  Dim X As Integer

S = Sheets("Temp").Range("C1").Value

  For X = 1 To S
  newname = Sheets("Temp").Range("A2").Value
  
Sheets.Add Type:=xlWorksheet
ActiveSheet.Name = newname
Sheets("Temp").Range("D1").Value = Sheets("Temp").Range("A1") - X

Next X

End Sub


Firstly, create a new workbook. There will be three sheet tabs showing (1,2,3). Name the first one "Temp". Delete sheets 2 and 3.

In the "Temp" sheet:-

In cell A1 enter the end date (31/12/2015). In cell B1, enter the start date (1/01/2015).
In cell C1, enter this formula:

=A1-B1+1

In cell D1, enter this formula:

=A1

In cell A2, enter this formula:-

=TEXT(D1,"dd.mm.yyyy")

Go to the Developer tab ---> Visual Basic --->Insert --->Module. Enter the above code (DateFill) in a standard module.

Run the macro and you should see the code populate 365 sheet tabs with all the dates.

You can then delete the "Temp" sheet.

As you will have alot of sheets in your workbook, you will find the easiest way to access them is to right click on the sheet tab scroll arrows down on the bottom left of any worksheet. This will bring up all the sheet names (dates) in your workbook in a pop-up menu for you to select. Doesn't matter which sheet you are on. To select the sheet that you want to work in from the pop-up menu, just double click on it and the tab will be highlighted in bold type.

Have fun!

Cheers,
vcoolio.
0