Reverse order of days auto-populated by macro that creates new sheet named 06/01, 06/02 and so on

Question

I am trying to set up a macro that when run will auto-create new sheets with names of dates in chronological order. EX: 06/01, 06/02 and fill the cells with data from a "Template" sheet. The macro currently will create these sheets in reverse order such as 06/30, 06/29..etc. How do I make it start from 06/01...06/30 instead of 06/30...06/01 with the new named sheets created?

I have tried the code listed below. Along with changing the

Sheets("Temp").Range("d5").Value = Sheets("Temp").Range("a5") - X

to

Sheets("Temp").Range("d5").Value = Sheets("Temp").Range("a5") + X

Setting up the code in VB for Temp Sheet:

--Start--

Sub Dtpopulate()

Dim S As Integer
Dim X As Integer
S = Sheets("Temp").Range("c5").Value

For X = 1 To S

newname = Sheets("Temp").Range("a6").Value

Worksheets("Template").Activate
       Sheets("Template").Cells.Select
    Selection.Copy


Sheets.Add.Name = newname
        Sheets(newname).Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, 
Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


Sheets("Temp").Range("d5").Value = Sheets("Temp").Range("a5") - X

Next X


End Sub

--Finish--

I expected the sheets to be created and auto-filled with the data from "Templates" and each sheet to be named 06/01...06/30, but the output are sheets named 06/30 to 06/01.

OK last question. What is the value of range a6? This is where you set the sheet name so I probably should have started there — urdearboy, Jun 04 '19 at 23:09
The value of a6 is `=TEXT(D5,"dd-mmm")` where D5 is equal to A5 which is the ending date value. Which in this case is 6/30. — PxlPrfctJay, Jun 05 '19 at 11:43
Here is the set up for the cells: Enter start date in cell “B5”, Enter end date in cell “A5”, Enter the following formula in cell “C5” =A5-B5+1, Enter the following formula in cell “D5” =A5, Enter the following formula in cell “A6” =TEXT(D5,"dd-mmm-yyyy") — PxlPrfctJay, Jun 05 '19 at 11:46

score 2 · Answer 1 · answered Jun 01 '19 at 00:04

2

If you step through your code you will notice that the sheets are created in the proper order: they are just not organized in order.

You will want each new sheet to be situated behind the most recently created one. You can read the deets about creating a new sheet here. The bit of code form this link that answers your question is:

Sheets.Add(After:=Worksheets(Worksheets.Count))

answered Jun 01 '19 at 00:04

urdearboy

14,439
5
28
58

I've tried adding Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = newname with no success. Any advice? – PxlPrfctJay Jun 03 '19 at 12:59
You have to name it separately when you do it this way. – urdearboy Jun 03 '19 at 13:35
Before or after the Sheet.Add.Name line? – PxlPrfctJay Jun 03 '19 at 15:04
You have to have the sheet before you can rename it so: **after**. [This](https://stackoverflow.com/questions/20697706/how-to-add-a-named-sheet-at-the-end-of-all-excel-sheets) link tells you exactly how to do that – urdearboy Jun 03 '19 at 15:28
I used `Dim ws As Worksheet Set ws = Sheets.Add(After:=Worksheets(Worksheets.Count)) ws.Name = newname` and it still orders them 6.30 to 6.01. What am I missing? – PxlPrfctJay Jun 03 '19 at 16:23
I think I see the problem. Super busy tho - can revisit later today! – urdearboy Jun 03 '19 at 17:36
Thank you so much! – PxlPrfctJay Jun 03 '19 at 17:52

score 0 · Answer 2 · answered Jun 10 '19 at 17:55

I figured out the solution to auto-populating each sheet with the corresponding data in the Templates worksheet along with creating a new sheet in order for each day of the month(i.e 6.1-6.30)

Sub Dtpopulate()

Dim S As Integer
Dim X As Integer
S = Sheets("Temp").Range("c5").Value

For X = 1 To S

newname = Sheets("Temp").Range("a6").Value



 Worksheets("Template").Activate
       Sheets("Template").Cells.Select
    Selection.Copy

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = newname
        Sheets(newname).Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False


Sheets("Temp").Range("d5").Value = Sheets("Temp").Range("a5") + X

Next X



End Sub

The excel spread sheet where the macro is one is set up with the following cells:

-Enter Start Date in cell A5 ex. 6/1
-Enter End Date in B5 ex. 6/30
-Enter the numbers of days in that month in C5 ex. 30
-Type "=A5" in cell D5
-In cell A6 enter "=TEXT(D5,"dd-mmm")"

After all information is entered run the macro and you will have 28-31 new worksheets all with the Template Worksheet data copied into each new worksheet.

Reverse order of days auto-populated by macro that creates new sheet named 06/01, 06/02 and so on

2 Answers2