Hello everyone. I am currently coding a project which requires information from a prior month. What I have is a message box which prompts the user to enter the date: dd-mmm-YYYY format. That value is then stored as a variable deontoed fDate. I would like to create a sub macro which takes fDate and"subtracts a month" and create a file which will be named after that, for instance if the current month is 30-jan-2010, then a file will be created and named 31-dec-2009. My line of thought was "fdate -1", but that returns an error. Please see the code below, specificaly Sub CreateFiles (). Any help is greatly appreciated.
Option Explicit
Dim fDate As String
Dim fPath As String
____________________________________________________
Sub CreateFolder()
Dim Fldr As String
Dim ErrBuf As String
fDate = Application.InputBox("Enter a date in the format shown:", "Date to add...", Format(Date, "DD-MMM-YYYY"))
If fDate = "False" Then Exit Sub
fPath = "L:\"
On Error GoTo ErrorHandler
Fldr = fPath & fDate & "_157"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "157_Reports"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "Support_Summaries"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Roll_forward_wTA"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Roll_forward_12m"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "157_Reports\" & "Terminated"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "157_Reports\" & "L3_IDA"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "157_Reports\" & "IBRD_L3"
MkDir Fldr
Fldr = fPath & fDate & "_157\" & "105_Reports"
MkDir Fldr
If Len(ErrBuf) > 0 Then MsgBox "The following folders already existed:" & vbLf & vbLf & ErrBuf
Exit Sub
ErrorHandler:
ErrBuf = ErrBuf & vbLf & Fldr
Resume Next
End Sub
_____________________________________________________
Sub MoveFilesFolder2Folder()
Dim fso
Dim sfol As String
Dim dfol As String
sfol = "L:\157_Support_Summaries"
dfol = fPath & fDate & "_157\" & "Support_Summaries"
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
If Not fso.FolderExists(sfol) Then
MsgBox sfol & " is not a valid folder/path.", vbInformation, "Invalid Source"
ElseIf Not fso.FolderExists(dfol) Then
MsgBox dfol & " is not a valid folder/path.", vbInformation, "Invalid Destination"
Else
fso.CopyFile (sfol & "\*.*"), dfol ' Change "\*.*" to "\*.xls" to move Excel Files only
End If
If Err.Number = 53 Then MsgBox "File not found"
End Sub
______________________________________________________
Sub CreateFiles()
Dim sPath1 As String
Dim sPath2 As String
Const sFileOut1 As String = "105_version1.xlsm"
Const sFileOut2 As String = "CDS.xlsm"
sPath1 = fPath & fDate & "_157\105_Reports\"
sPath2 = fPath & fDate & "_157\Support_Summaries\"
Workbooks.Add
With ActiveSheet
.Name = fDate & "_105_v1"
.Parent.SaveAs Filename:=sPath1 & sFileOut1, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
.Parent.Close
End With
Workbooks.Add
With ActiveSheet
.Name = fDate - 1 & "_CDS" <------------------------------------- Error
.Parent.SaveAs Filename:=sPath2 & sFileOut2, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
.Parent.Close
End With
End Sub
Bookmarks