Macro to create new month from existing template

Hi All

I have a template log book for one of our specialist teams.

I would ideally like to have a control sheet in the tempalte where the user would be able to click a button that would prompt the user to select the name of the new month and the name.

The macro would then take the 6 workbooks i have in the Template file and, copy them (formulae and formats and all) and save the workbook a specific location (CLogging\New Logging) with the new workbook called Month Year.xls

Any help in this matter would be appreciated

Hi,

Could you please provide example workbooks.

Hi All

Found the code i needed, thought it could be useful for someone else.


Sub CraigC_MACRO()

Dim MonthName As String
Dim YearField As String
Dim MonthArray(1 To 13) As String
MonthArray(1) = "January"
MonthArray(2) = "February"
MonthArray(3) = "March"
MonthArray(4) = "April"
MonthArray(5) = "May"
MonthArray(6) = "June"
MonthArray(7) = "July"
MonthArray(8) = "August"
MonthArray(9) = "September"
MonthArray(10) = "October"
MonthArray(11) = "November"
MonthArray(12) = "December"
MonthArray(13) = "End"
Dim DateField As String

CurWkBk = ActiveWorkbook.Name
CurFolder = ActiveWorkbook.FullName
L2 = Len(CurFolder)
L1 = Len(CurWkBk)
ActFolder = Left(CurFolder, L2 - L1)

MonthName = InputBox("Enter New Month")

X = 1

While X < 14
If MonthName = MonthArray(X) Then
GoTo NextPart
Else
If MonthArray(X) = "End" Then
MsgBox ("The date you have entered is incorrect, please check the spelling")
Exit Sub
Else
X = X + 1
End If
End If
Wend

NextPart:

DateField = Date

YearField = InputBox("Enter Year (YYYY Format)")

ActiveWorkbook.SaveAs (ActFolder & "EM WM Logging " & MonthName & " " & YearField & ".xls")
WKBK = "EM WM Logging " & MonthName & " " & YearField & ".xls"
Sheets("Control").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
ActiveWorkbook.Save
'ActiveWorkbook.Close
Application.Quit


End Sub