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
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