Macro, how to SaveAs a file with a new name in the same directory where the main file

[k0st4din]

Hello everyone,
I have this macro that takes the text from specific cells and makes it the title of the excel workbook. However, I can't seem to get around telling it to save to the same directory it's in. Because in a certain folder on the computer I open which is called Master, for example. I have quite a few macros in it. My idea is to save this same file (so as not to lose the macros) but already with a new title. Save As..... - I change the name, I already have things written inside, I have a new file and everything is in it. The Master file remains, waiting to be reopened and new information written.
I would appreciate any help from you. It is very important that it automatically goes to the folder, and not for me to give it, write a directory.....

    Sub SaveAsA1()
ThisFile = Range("J1").Value & " " & Range("C2").Value & " " & Range("B4").Value & " " & Range("A4").Value
ActiveWorkbook.SaveAs fileName:=ThisFile
Application.GetSaveAsFilename
End Sub

[jdelano]

This works for me

    Dim folderPath As String
    Dim newFileName As String
    
    folderPath = Application.ActiveWorkbook.Path & "\" ' get the workbooks current file location
   
    newFileName = folderPath & Range("E30").Value & " " & Range("F30").Value & " " & Range("G30").Value & " " & Range("H30").Value & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=newFileName
    
    MsgBox "The file has been saved as " & newFileName

[k0st4din]

Hello jdelano,
The macro is unique and exactly what I was trying to do.
You are a unique Person who helps me once again.
Infinite thanks to you and your possibilities.
I wish you only health and countless happy moments!

[k0st4din]

Just one last thing, if I have 12 worksheets, how do I tell those same cells, but to take and save the info from the active worksheet.
For example, I insert information into a Peaches worksheet - it takes the information from there and saves the file. Then I go to another worksheet and take the info from there. Because sometimes the data is different in different worksheets.
Thanks in advance!

[Aussiebear]

Are you wanting each sheet to be saved when its the active sheet?

[k0st4din]

Hi, this came to me at the last minute as an idea.
I want (the macro works perfectly) and it saves absolutely everything - all worksheets, macros, etc.
If in the workbook I have 12 worksheets and I work in worksheet 3, just take the information that is written in it and again save the whole workbook, if I work in worksheet for example 11, take the information from said cells and save the entire workbook again.
Just like SaveAs.....
I don't want to save only the specific worksheet, but the whole excel file
Thank you very much!

[jdelano]

You could do it like this:

Sub SaveWkAs(sheetName as String)
    
    Dim folderPath As String
    Dim newFileName As String
    Dim ws as Worksheet

    Set ws = ThisWorkbook.Worksheets(sheetName)

    folderPath = Application.ActiveWorkbook.Path & "\" ' get the workbooks current file location
   
    newFileName = folderPath & ws.Range("E30").Value & " " & ws.Range("F30").Value & " " & ws.Range("G30").Value & " " & ws.Range("H30").Value & ".xlsm"    ActiveWorkbook.SaveAs Filename:=newFileName
    
    MsgBox "The file has been saved as " & newFileName
End Sub

Then when you call the Sub just pass the name of the sheet you want it to derive the file name from.

SaveWkAs("Sheet11")

[Aussiebear]

What happens if one of the cell values is blank? Say F30 for some reason was blank... Can you have 3 spaces in a file name?

[jdelano]

They'll need to add some error trapping and testing conditions to meet their needs, of course.

EDIT: I should not assume that that is something they're already planning though.

[Aussiebear]

Poking around the Internet I came across this little bit of code from Extend Office

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Update by ExtendOffice 20220907
Dim xFileName As String
Dim xStr As String
Dim xStrWSH As String
Dim xWSh As Worksheet
Dim xWShs As Sheets
Dim xWSh1 As Worksheet
Dim xWB As Workbook
xStrWSH = "xHidWSH_LJY"
On Error Resume Next
Set xWB = Application.ActiveWorkbook
Set xWShs = xWB.Worksheets
Set xWSh = xWShs.Item(xStrWSH)
If xWSh Is Nothing Then
Set xWSh1 = xWShs.Add
xWSh1.Name = xStrWSH
xWSh1.Visible = xlSheetVeryHidden
Cancel = False
Else
If Trim(Application.Sheets("Sheet1").Range("A1").Value) = "" Then
Cancel = True
MsgBox "Save cancelled"
End If
End If
End Sub

and was wondering if the line

If Trim(Application.Sheets("Sheet1").Range("A1").Value) = "" Then

could be altered (Adapted) to cover three cells as the criteria?

[k0st4din]

Hi, according to my calculations, there shouldn't be any empty cells.
That's why I try to describe everything in detail. You know there's always some stupid bug, but hopefully it works the way I want it to.
You are wonderful people.
Thank you very much!

[jdelano]

You're welcome, happy to lend a hand! Good luck with the remainder of your project.

[k0st4din]

Just lastly, when I put the macro in a module, I can't order it to a button.
Or put the macro in each worksheet by renaming the worksheets here -> (sheetName as String), renaming Nuts, Bananas, Peaches
Sorry, I don't get it

[jdelano]

It depends on how you need to tell the macro which sheet to use when renaming it. You can have a dedicated sheet for running this code, sat the first sheet in the workbook has a button that says "Save Workbook As" then you can pop up an inputbox or use a specific cell on that sheet for the name, to allow the person to type in the sheet to use, lastly you could use a Userform, which displays a list of sheets, to allow them to select which one to use.

Using a dedicated sheet for the macro to run would be my first choice, and have a cell to hold which sheet name to grab the filename information from.
Put the Sub procedure in the sheet VB and the button

Public Sub SaveWkAs(sheetName As String)
    
    Dim folderPath As String
    Dim newFileName As String
    Dim ws As Worksheet


    Set ws = ThisWorkbook.Worksheets(sheetName)


    folderPath = Application.ActiveWorkbook.Path & "\" ' get the workbooks current file location
   
    newFileName = folderPath & ws.Range("E30").Value & " " & ws.Range("F30").Value & " " & ws.Range("G30").Value & " " & ws.Range("H30").Value & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=newFileName
    
    MsgBox "The file has been saved as " & newFileName
End Sub




Private Sub btnSaveTheWorkbook_Click()
    Call SaveWkAs(Range("A3"))
    
End Sub

[k0st4din]

Ok, this in my case means that I will have to make 12 macros (the same) but with different names for example in SUB .
Why?
Because it is about people who are literally explained to them - Exactly in which cell, what they have to write, which is very tiring for me and I try to help myself, make my work easier when I get the file.
The case study is the following: I have 12 worksheets from January to December.
In each worksheet, leave days are filled in specific cells. We solved this by taking information from which cells to form my title.
If the person wants to use leave in, for example: the month of June. He goes into the June worksheet, fills in the necessary dates, days, etc. and I already have the information, the person what he wants, therefore, he saves the file with all the worksheets, but the file remains (when I open it in the June worksheet) and I immediately see what he wants, etc. When, for example, he decides to use leave in the month of September, he opens the September worksheet, writes down what he needs and I already have other information, respectively in this worksheet (as the last information) I put it in the title of the excel file.
Maybe I'm not expressing myself properly or explaining it properly and maybe that's why I'm talking about somehow .ActiveWorksheet


12023-12-07_144840.jpg

Or the other option is to define a cell in each worksheet, in which to write January worksheet in cell A40 to write January, in March worksheet in cell A40 to write March, and so on for each of the 12 working sheet to have one macro but take the info from the Active Worksheet

[k0st4din]

Yes, yes, this explanation of mine above is unnecessary.
That's exactly what I did, in each worksheet I wrote his name and now it works super perfectly.
Hey you guys are amazing!!!
Thank you from the bottom of my heart!
Bow!

[Aussiebear]

Sorry robinho0d but my first impression is that this is an extremely complicated workbook to manage. You mention that [QUOTE]Because it is about people who are literally explained to them - Exactly in which cell, what they have to write, which is very tiring for me and I try to help myself, make my work easier when I get the file./QUOTE]

There are four simple pieces of information you require from the employee namely, their name, the start of the time off, the return to work date, and the expected number of days away from work. That could all be done on one sheet, they click on Save, and the relevant data is copied to another master sheet where you then compare the request to see if it fits the operational needs of the company.

Right now it appears that you will end up with multiple workbooks, making your job more stressful. But hey, what ever floats your boat.

[k0st4din]

Hi Aussiebear,
to be honest at first it was like or almost like you describe it, I had the hope that everything would be fine. However, at one point it turned out that they don't even remember, they don't save their last sent file and a bunch of similar things, which on the other hand, they burdened me to dig into their old files to see where the truth is. Well, this is off-topic from macros because you are giving me some suggestion, for which I thank you very much. At one point I decided that if I put together, in other words, a common table for the whole year, it would be easier for them, by opening the same table, but only changing the month in which they want to use days off. More or less things fell into place, but they began to get lost in the instructions, how exactly to write down the titles of the tables themselves, and the result is in the face, we got here and to this solution, which, will reduce things for them, just to press a button and to send me the "complicated" table. I agree with that about the boat, but sometimes that's life and we try to adjust to it. Thank you both from the bottom of my heart for your help. I wish you only health and wonderful moments. See you soon....