dcsimg
Results 1 to 16 of 16

Thread: Save workbook with macro.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Save workbook with macro.

    I have the following macro in a workbook.

    Everything works great except for the row that saves the workbook before it closes.

    What have I missed??

    I am far from an expert so I am grateful for any help I can get.



    The macro looks like this:
    Code:
     Sub Makro1()
    '
    ' Utskrift_radering Makro
    '
    Sheets("Följesedel").Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Sheets("Registrering_utskrift").Select
        
        Dim filnamn As String
    
        filnamn = Range("I2").Value
    
        ActiveWorkbook.SaveAs Filename:="C:\Ekonomi" & filnamn & ".xlsm"
       
        Range("I31").Select
        Selection.ClearContents
        Range("A33:H34").Select
        Selection.ClearContents
        Range("C3:F3").Select
    
        ActiveWorkbook.Save
        
        ActiveWorkbook.Close savechanges:=True
    
    End Sub
    Last edited by Nisse Tudare; Aug 21st, 2019 at 12:40 PM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Save workbook with macro.

    ActiveWorkbook.Save

    ActiveWorkbook.Close savechanges:=True
    i can not think of any reason that line should work, but as the next line also saves the workbook on closing, do you really need to save it twice
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Thank you for your answer.
    As I've mentioned before, I'm no expert.
    You're right, the file doesn't need to be saved twice.

    Short explanation what I want the macro to do:

    1. Print sheet "Följesedel".
    2. Save sheet "Registrering_utskrift" with the value in I2 as name in "C:\Ekonomi"
    3. Clear some cells.
    4. Save the whole workbook in it's current folder.
    5. Close the workbook.

    It has to be in this sequence to work for me.

    I apologize for being unclear.

    Code:
     
    Sub Makro1()
    '
    ' Utskrift_radering Makro
    '
    Sheets("Följesedel").Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Sheets("Registrering_utskrift").Select
        
        Dim filnamn As String
    
        filnamn = Range("I2").Value
    
        ActiveWorkbook.SaveAs Filename:="C:\Ekonomi" & filnamn & ".xlsm"
       
        Range("I31").Select
        Selection.ClearContents
        Range("A33:H34").Select
        Selection.ClearContents
        Range("C3:F3").Select
        
        ActiveWorkbook.Close savechanges:=False
    
    End Sub
    This code works with one problem.
    The workbook dosen't save as I mentioned paragraph 4 above.

    If it help, I can send you the file.

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Thank you for your answer.
    As I've mentioned before, I'm no expert.
    You're right, the file doesn't need to be saved twice.

    Short explanation what I want the macro to do:

    1. Print sheet "Följesedel".
    2. Save sheet "Registrering_utskrift" with the value in I2 as name in "C:\Ekonomi"
    3. Clear some cells.
    4. Save the whole workbook in it's current folder.
    5. Close the workbook.

    It has to be in this sequence to work for me.

    I apologize for being unclear.

    Code:
     
    Sub Makro1()
    '
    ' Utskrift_radering Makro
    '
    Sheets("Följesedel").Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Sheets("Registrering_utskrift").Select
        
        Dim filnamn As String
    
        filnamn = Range("I2").Value
    
        ActiveWorkbook.SaveAs Filename:="C:\Ekonomi" & filnamn & ".xlsm"
       
        Range("I31").Select
        Selection.ClearContents
        Range("A33:H34").Select
        Selection.ClearContents
        Range("C3:F3").Select
        
        ActiveWorkbook.Close savechanges:=False
    
    End Sub
    This code works with one problem.
    The workbook dosen't save as I mentioned paragraph 4 above.

    If it help, I can send you the file.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Save workbook with macro.

    The workbook dosen't save as I mentioned paragraph 4 above.
    as i mentioned above when you close the workbook you had specified to savechanges, as that is saving the workbook, no need to save before that, check the time stamp of the file after

    If it help, I can send you the file.
    if you post the file (zip first) i will test to see if i can find why there is a problem
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Thank you for your answer.

    I will post the file.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Thank you for your answer.

    I have attatched the original file.
    This file works with one exception. The line "ActiveWorkbook.Save" at the end of the code does not work.
    I have a similar workbook with the same code that works.
    The problem is I don't understand why.
    Attached Files Attached Files
    Last edited by Nisse Tudare; Aug 22nd, 2019 at 01:19 PM.

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Thank you for your answer.

    I will post the file.

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Save workbook with macro.

    i tested your code from the sample workbook

    i got 2 errors, one on workbook open
    thought it did work correctly after it had been saved
    at workbook open there is no activesheet, you need to qualify the range with the sheet name, which is good practice anyway
    Code:
     Sheets("Följesedel").Range("I2").Value = Sheets("Följesedel").Range("I2").Value + 1 'Ändra cell efter behov.

    the other was trying to saveAs to a none existent folder, easy fix
    i got no other errors and the workbooks were saved

    just in case you are expecting the active workbook that is open initially to be saved, you code does not do that
    after saveAs the active workbook is the newly opened workbook, the original is closed without saving
    if that is the case, you should save before saveAs

    i would also recommend to avoid selecting ranges or using the selection object or active anything, just work with fully qualified ranges

    1. Print sheet "Följesedel". OK
    2. Save sheet "Registrering_utskrift" with the value in I2 as name in "C:\Ekonomi" The entire work book is saved to the number /name and it becomes the active workbook
    3. Clear some cells. Not in the original workbook
    4. Save the whole workbook in it's current folder. Saving the numeric copy again, less cleared cells
    5. Close the workbook.


    i think this is what you are trying to do
    Code:
    Sub Makro1()
    '
    ' Utskrift_radering Makro
    ' Skriver ut följesedel och raderar uppgifter.
    '
    ' Kortkommando: Ctrl+q
    Dim ru As Worksheet, nubook As Workbook
        Dim filnamn As String
        Sheets("Följesedel").PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Set ru = Sheets("Registrering_utskrift")
        
    
        filnamn = ru.Range("I2").Value
        Set nubook = Workbooks.Add
        With nubook
            ru.Copy .Sheets(1)
            Application.DisplayAlerts = False
            For s = 2 To .Sheets.Count
                .Sheets(s).Delete          ' remove any original sheets in the new workbook
            Next
            Application.DisplayAlerts = True
            .SaveAs Filename:="C:\temp\" & filnamn & ".xlsx"
            .Close False
        End With
    
        With ru
            .Range("C3:F3").ClearContents
            .Range("H3:I3").ClearContents
            .Range("C4:I4").ClearContents
            .Range("A6:G6").ClearContents
            .Range("B7:G7").ClearContents
            .Range("I6").ClearContents
            .Range("A8:H9").ClearContents
            .Range("A11:G11").ClearContents
            .Range("B12:G12").ClearContents
            .Range("I11").ClearContents
            .Range("A13:H14").ClearContents
            .Range("A16:G16").ClearContents
            .Range("B17:G17").ClearContents
            .Range("I16").ClearContents
            .Range("A18:H19").ClearContents
            .Range("A21:G21").ClearContents
            .Range("B22:G22").ClearContents
            .Range("I21").ClearContents
            .Range("A26:G26").ClearContents
            .Range("B27:G27").ClearContents
            .Range("I26").ClearContents
            .Range("A28:H29").ClearContents
            .Range("A31:G31").ClearContents
            .Range("B32:G32").ClearContents
            .Range("I31").ClearContents
            .Range("A33:H34").ClearContents
    '        .Activate
    '        .Range("C3:F3").Select
        End With
        ActiveWorkbook.Save
        ActiveWorkbook.Close savechanges:=False
    End Sub
    i tested this it seems to work correctly
    test carefully, in case i am mistaken about your intentions
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Thank you for your answer.

    I've got a Runtime error 9 "The index is out of range" on the line " .Sheets(s).Delete ' remove any original sheets in the new workbook"

    Then the macro stops.

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Thank you for your answer.

    I've got a Runtime error 9 "The index is out of range" on the line " .Sheets(s).Delete ' remove any original sheets in the new workbook"

    Then the macro stops.

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Save workbook with macro.

    when you open a new workbook how many sheets does it have? you should be able to see when the code stops you have a book1 (or whatever number and translation) how many sheets in it and what is the value of s? have some sheets been removed? do you have all the . in front of sheets in 2 places
    you can comment the lines below, just you may have some blank sheets in the saved workbook
    Code:
            Application.DisplayAlerts = False
            For s = 2 To .Sheets.Count
                .Sheets(s).Delete          ' remove any original sheets in the new workbook
            Next
            Application.DisplayAlerts = True
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    I'm not really sure what you mean.
    When I open a brand new workbook there are three worksheets.
    I do not understand what this means in this context.
    The workbook that I have problems with has two worksheets, "Följesedel" and "Registrering_utskrift".
    I've copied / pasted the code you sent earlier, so I don't think I've missed anything.
    Attaches the file with your new code.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    I'm not really sure what you mean.
    When I open a brand new workbook there are three worksheets.
    I do not understand what this means in this context.
    The workbook that I have problems with has two worksheets, "Följesedel" and "Registrering_utskrift".
    I've copied / pasted the code you sent earlier, so I don't think I've missed anything.
    Attaches the file with your new code.
    Attached Files Attached Files

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,447

    Re: Save workbook with macro.

    I'm not really sure what you mean.
    when you add a new workbook then cop a sheet into it, gives a total of 4 sheets, it was just housekeeping to remove the original empty sheets, that was where the error occurred

    i tested the code in the sample in post #14, it all worked as intended, without error (excel 2013, english)
    the only issue i could see, i forgot to change the folder name from what i used on my system, so you would need to change that back

    the only other thing i could think of, if there is some difference in the language version of excel

    here is an alternative to that part of the code, also tested to run without error and produce the same result
    Code:
        filnamn = ru.Range("I2").Value
         ru.Copy
         Set nubook = Workbooks(Workbooks.Count)
         nubook.SaveAs Filename:="C:\temp\" & filnamn & ".xlsx"
         nubook.Close False
    
    '''    Set nubook = Workbooks.Add
    '''    With nubook
    '''        ru.Copy .Sheets(1)
    '''        Application.DisplayAlerts = False
    '''        For s = 2 To .Sheets.Count
    '''            .Sheets(s).Delete          ' remove any original sheets in the new workbook
    '''        Next
    '''        Application.DisplayAlerts = True
    '''        .SaveAs Filename:="C:\temp\" & filnamn & ".xlsx"
    '''        .Close False
    '''    End With
    
        With ru
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Jun 2018
    Location
    Sweden
    Posts
    20

    Re: Save workbook with macro.

    Excuse the delay with the answer.
    Now everything works exactly as I want.
    A big thank you for all your help.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width