Hello,
My name is Jonas, and my first post here.. I'm trying to learn more Excel, so happy to be here with so many knowledgeable people.
I'm trying to make an overtime sheet for my job... In the H4 cell I have the date in the format Feb. 2014 (but filled in with 2.14) ... There I want to fill in the date for each month
when I make my overtime sheet to send to my manager... I want to make a macro function that each time I have populated my monthly overtime sheet, I click a "save button" and it saves a copy of that monthly overtime sheet to a folder called "saved lists" that will be placed in a folder "overtime" in my desktop. The name of the saved file has to be the value in H4.xlsm, and the next months file has to change according to the value in H4... In the "overtime" folder I want to have the main overtime file which I will use/open each month and populate, click the "save button" (I mean a macro button in the excel sheet, and not normal excel save), and have it save that months overtime list in the folder "saved lists" with the name of the corresponding month as given by H4..
In short, I want a macro that helps me populate my overtime hours, and be able to click "save" and have it make a copy of each months overtime list in the folder as explained above..
The file I've made can be downloaded here - https://dl.dropboxusercontent.com/u/.../Overtime.xlsm --- if you can/have time pls. change it for me and send to [email protected] and also just paste your changes so other people can learn ... Here the macro as I have it now... Now it saves the file in C:\Users\Jonas\Documents\Saved overtime lists with the name 01.02 (for 1st of feb.) instead of the value as given in H4 which is Feb. 2014
Sub Save_wrkbk()
Dim strFilename, strDirname, strPathname, strDefpath As String
strDirname = "Saved overtime lists"
strFilename = Range("H4").Value
strDefpath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\"
If IsEmpty(strFilename) Then Exit Sub
MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename
ActiveWorkbook.SaveAs Filename:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
Thanks in advance !!!
Bookmarks