Consulting

Results 1 to 18 of 18

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

  1. #1

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

    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

  2. #2
    VBAX Regular
    Joined
    Sep 2023
    Posts
    78
    Location
    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
    Attached Images Attached Images

  3. #3
    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!

  4. #4
    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!

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,939
    Location
    Are you wanting each sheet to be saved when its the active sheet?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  6. #6
    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!

  7. #7
    VBAX Regular
    Joined
    Sep 2023
    Posts
    78
    Location
    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")

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,939
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    VBAX Regular
    Joined
    Sep 2023
    Posts
    78
    Location
    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.

  10. #10
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,939
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  11. #11
    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!

  12. #12
    VBAX Regular
    Joined
    Sep 2023
    Posts
    78
    Location
    You're welcome, happy to lend a hand! Good luck with the remainder of your project.

  13. #13
    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

  14. #14
    VBAX Regular
    Joined
    Sep 2023
    Posts
    78
    Location
    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
    Attached Images Attached Images

  15. #15
    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

  16. #16
    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!

  17. #17
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,939
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  18. #18
    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....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •