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 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - 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.

:))
Related:

1 response

vcoolio Posts 1410 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
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