Monthly Billing with Master sheet VBA Copy and create

CRVerdusco

New Member
Joined
Nov 22, 2013
Messages
6
Does anyone know how to sort after a loop command using the same macro?
Can you also in a loop command add a sheet reference in a cell that references the sheet you just created?
I am trying to create a workbook that has a worksheet for everyday and then a master sheet at the end. I have the template for the daily sheet done and have found code to get it copied for each day of the month as well.
What I am having trouble with now is in the Master sheet I insert a row with for each day but I can't get it to sort it so it's in the monthly order. I also need to have that Monthly totals sheet take the totals from each daily sheet and input the formula as it creates each new row. I have the section trying to sort the cells, after the loop it works on it's own but won't work where it is. I tried moving it after "End It:" but I get a debug error. Where it is currently I get no errors but it also doesn't take the command. This is what I have so far
Code:
Sub CreateMonthly()
Dim strDate As String
Dim NumDays As Long
Dim i As Long
Dim sh As Object
Dim wsBase As Worksheet
On Error GoTo EndIt
    
' The Do statement to captures Month/Year via Input Box
' and return number of days in the month to the NumDays variable
    Do
        strDate = Application.InputBox( _
            Prompt:="Please enter month and year: mm/yyyy", _
            Title:="Month and Year", _
            Default:=Format(Date, "mm/yyyy"), _
            Type:=2)
 
        If strDate = "False" Then Exit Sub
        If IsDate(strDate) Then Exit Do
        If MsgBox("Please enter a valid date, such as ""01/2013""." _
            & vbLf & vbLf & "Invalid Date. Please enter valid date (01/2013)", vbYesNo + vbExclamation, _
            "Invalid Date") = vbNo Then End
    Loop
    
    Application.ScreenUpdating = False
    NumDays = Day(DateSerial(Year(strDate), Month(strDate) + 1, 0))
    Set wsBase = Sheet1
    
' For each day, the For statement below copies the template sheet 'n' times
    For i = 1 To NumDays
        wsBase.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = Format(DateSerial(Year(strDate), Month(strDate), i), "dd")
        ActiveSheet.Range("a1:f1") = Format(DateSerial(Year(strDate), Month(strDate), i), "yyyy-mmm-dd dddd")
        Sheets(2).Range("4:4").Offset(1).Insert Shift:=xlUp, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets(2).Range("a4").Offset(1).Value = Format(DateSerial(Year(strDate), Month(strDate), i), "dd")
    Next i
    
    Sheets(2).Range("5:40").sort Key1:=Range("A5"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Sheets(2).Range("4:4").Delete
EndIt:
    Application.ScreenUpdating = True
    
End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,215,364
Messages
6,124,507
Members
449,166
Latest member
hokjock

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top