Button to save workbook, with value in specific cell (H5), in a specific folder

[markeeny]

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

[moinsk21]

Hi,

As you are New to forum, this is to inform you that next time if you add any codes to the post. Kindly add it in special function added in the post tools (the sign is of #)

Please find the updated code.

Please Login or Register  to view this content.

Thanks!
Moinuddin Shaikh

If you like my Response:
1. Show appreciation to those who have helped you by clicking "* Add Reputation" below their posts.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

Appreciate someone towards their career path is better than saying Thanks!
-Moin