josullivan601632
New Member
- Joined
- Aug 23, 2020
- Messages
- 39
- Office Version
- 2007
- Platform
- Windows
Hi all
I have the following macro I run to save a file with filename derived from cells contents within the workbook and it works lovely
Of course, I have now developed the use of this file and would like to update this code so it creates a folder first and then saves the workbook in that folder, folder name to be same a workbook,
Sheets("Costing").Range("B1").Value & " " & Sheets("Costing").Range("A1").Value & " " & Sheets("Costing").Range("A2") as below (currently used for the workbook name).
I can't do it though
...can anybody help to update my existing code:
Sub SaveWorkbook()
Dim strTempFile As String
Dim strData As String
Dim spath As String
spath = Environ("USERPROFILE") & "\Newman Refrigeration Ltd\Newman Refrigeration Ltd - General\Contracting\2. Jobs\Current Jobs\ "
strData = Sheets("Costing").Range("B1").Value & " " & Sheets("Costing").Range("A1").Value & " " & Sheets("Costing").Range("A2").Value & ".xlsm"
strTempFile = spath & strData
MsgBox strData, , "This has been saved in the Current Jobs folder under filename:"
ActiveWorkbook.SaveCopyAs Filename:=strTempFile
End Sub
Would be really grateful, it is beyond me but would make a massive difference!!!!
Thank you.
Jo
I have the following macro I run to save a file with filename derived from cells contents within the workbook and it works lovely

Of course, I have now developed the use of this file and would like to update this code so it creates a folder first and then saves the workbook in that folder, folder name to be same a workbook,
Sheets("Costing").Range("B1").Value & " " & Sheets("Costing").Range("A1").Value & " " & Sheets("Costing").Range("A2") as below (currently used for the workbook name).
I can't do it though

Sub SaveWorkbook()
Dim strTempFile As String
Dim strData As String
Dim spath As String
spath = Environ("USERPROFILE") & "\Newman Refrigeration Ltd\Newman Refrigeration Ltd - General\Contracting\2. Jobs\Current Jobs\ "
strData = Sheets("Costing").Range("B1").Value & " " & Sheets("Costing").Range("A1").Value & " " & Sheets("Costing").Range("A2").Value & ".xlsm"
strTempFile = spath & strData
MsgBox strData, , "This has been saved in the Current Jobs folder under filename:"
ActiveWorkbook.SaveCopyAs Filename:=strTempFile
End Sub
Would be really grateful, it is beyond me but would make a massive difference!!!!
Thank you.
Jo