Sunday, 4 November 2012

How To Create a Monthly Task Workbook with All Day Sheets

Excel VBA Macro To Create a Monthly Task Workbook with All Day Sheets
'This Macro Create Folder For the Curret Month(Eg: Noveber_2012)
'This Macro Creates a Workbook For the Current Month In MonthFolder(Eg: Noveber_2012).
'The Work Book Contains 30/31 Sheets Based on No.of Days in a Month
'Each Sheet Name Begins From 1st Day of Month to Last Day(Eg:01-11-2012.....30-11-2012)
'Note: This Macro Should use only once In a Month

Sub MonthlyTaskBook()

Dim D As String
Dim M As String
Dim MN As String
Dim Y As String
Dim P As String

Dim FSO As Object

Dim MyPath As String
Dim A As Integer

Application.DisplayAlerts = False


D = Day(DateSerial(Year(Date), MONTH(Date) + 1, 1) - 1)

'Counts No.of Days In a Month
M = MONTH(Date)
MN = MonthName(M)
Y = Year(Date)
P = "C:\Documents and Settings\Administrator\My Documents\"

Set FSO = CreateObject("Scripting.FileSystemObject")


If FSO.FolderExists(MyPath) = True Then

MsgBox MyPath & " Folder Already Exist In Given Path", VbOkCancel, "Filder Already Exits"
Exit Sub
End If


MkDir P & MN & "_" & Y

Workbooks.Add.SaveAs P & MN & "_" & Y & "\" & MN & "_" & Y & ".xlsx"

Workbooks(MN & "_" & Y).Activate


For A = D To 1 Step -1

ActiveWorkbook.Sheets.Add.Name = A & "-" & Left(MN, 3) & "-" & Y
Next A
For A = 1 To 3
ActiveWorkbook.Sheets("Sheet" & A).Delete
Next A
Workbooks(MN & "_" & Y).Save
Workbooks(MN & "_" & Y).Close

End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts