Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 32

Thread: Save a copy of a workbook with a new name
Thanks Thanks: 0 Likes Likes: 0

  1. #21
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Can you tell me how to keep the macro from displaying an error if I hit CANCEL if I change my mind? It also leaves behind the newly copied workbook when it errors out.
    Last edited by AndyTampa; Aug 20th, 2019 at 04:21 PM.

  2. #22
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    Can you tell me how to keep the macro from displaying an error if I hit CANCEL if I change my mind? It also leaves behind the newly copied workbook when it errors out.
    try this, i added an if statement where if the value of whats entered in the box (or cancel is pressed) is 0 it won't execute the code to copy and save.
    so hopefully you won't be naming the workbook "0"

    Code:
    Sub referenceFIX()
      Dim newWB As String
      Dim fold As String: fold = "C:\filepath\"
      
    newWB = InputBox("Enter a save name")
    If newWB <> 0 Then
    ActiveWorkbook.Sheets.Copy
      With ActiveWorkbook
        .SaveAs fold & newWB & Format(Date, "MM-DD-YYYY") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        .Close False
      End With
    Else
    End If
    End Sub
    Last edited by BlakeSkate; Aug 21st, 2019 at 09:40 AM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  3. #23
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Tried it. I got a Run-time error '13': Type mismatch. I assumed it was the 0 in the If statement and changed it to quotes just like in formulas. It worked. I also liked my code where I had a message in the InputBox say "Enter new filename", so I combined yours with what I had found to make this:
    Code:
    Sub CopyWorkbook()
      Dim strAFN1 As String, strAFN2 As String, strAFN3 As String, strAFN4 As String, strResult As String
      Dim strPath As String: strPath = "Q:\Work Performed\"
      
    strResult = InputBox(strAFN1 & vbCrLf & vbCrLf & strAFN2 & vbCrLf & strAFN3 & vbCrLf & strAFN4 & vbCrLf, "File copied to...", "Enter new filename")
    If strResult = "" Or strResult = "Enter new filename" Then Exit Sub
    ActiveWorkbook.Sheets.Copy
      With ActiveWorkbook
        .SaveAs strPath & strResult & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        .Close False
      End With
    End Sub
    This seems to work for me unless you can see some unintended consequences or have a way to streamline it. As near as I can tell, this can be used on any spreadsheet I want to save. It doesn't ask me for a path in case I want to choose a different one, but that's a macro for another day. Do you have any further insights on this?

  4. #24
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    I'm testing the savepath option with:
    Code:
    Sub TEST_SAVE_COPY_WITH_PATH()    Dim strAFN1 As String, strAFN2 As String, strAFN3 As String, strAFN4 As String, strResult As String
        Dim strPath As Variant
        
        strAFN1 = "Enter the file's new name."
        strAFN2 = "Do NOT include the file extension!"
        strAFN3 = "The file will be saved as a .xlsm file"
    
    
    strResult = InputBox(strAFN1 & vbCrLf & vbCrLf & strAFN2 & vbCrLf & strAFN3 & vbCrLf, "File copied to...", "Enter new filename")
    If strResult = "" Or strResult = "Enter new filename" Then Exit Sub
    ActiveWorkbook.Sheets.Copy
        With ActiveWorkbook
        strPath = Application.GetSaveAsFilename(strResult, "Excel Files (*.xlsm), *.xlsm")
            If strPath <> False Then
                ActiveWorkbook.SaveAs strPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled
                ActiveWorkbook.Close SaveChanges:=False
            Else
                ActiveWorkbook.Close SaveChanges:=False
            End If
        End With
        
    End Sub
    What do you think of this? I'd like to customize the SaveAs dialog box with a different title to direct the individual to pick a folder, but I can't figure that one out yet. I'm pretty sure it's in the SaveAs options, but I don't know which one or how to format it.

    I've also added code to close the new workbook when it's saved or if Cancel is pressed. I'm sure this could be written better and I'd appreciate any feedback.

  5. #25
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    I'm testing the savepath option with:
    I've also added code to close the new workbook when it's saved or if Cancel is pressed. I'm sure this could be written better and I'd appreciate any feedback.
    if it works it works.
    the only other thing i could say is where you want the application save as box opened?
    you could have it open with the path that the workbook is located in (as in most cases)

    I use Application.FileDialog
    it allows things like

    Code:
        With Application.FileDialog(msoFileDialogFilePicker)
            .Title = "Pick a save desination"
            .Filters.Add "Excel Files", "*.xlsm*"
            .AllowMultiSelect = False
            .InitialFileName = ThisWorkbook.Path & "\"
            If Not .Show Then Exit Sub
            wfile = .SelectedItems.Item(1)
            wdiag = InStrRev(wfile, "\")
            wpath = Left(wfile, wdiag)
            wbook = Mid(wfile, wdiag + 1)
        End With
    which is an altered version of what i'm using to select a file to vlookup in (this portion just allows me to pick a file from where my workbook is located)
    where you would need to change whats in bold to msoFileDialogSaveAs in order to save a file as opposed to picking a file.
    try this out with its many options if you want more customization
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  6. #26
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Now we're getting into deeper waters and I'm having trouble staying afloat.

    I definitely don't want to save to the same path as the original. The original is a tool I use, but the saved document will be a product of the tool. My work and my tools reside in different folders or else I'd get lost looking for things. That said, I have a coworker who might want to use this same macro but she named her folders her way. I would like to customize the save window title and maybe add the filters.

    I'm guessing that what you use is similar to what I posted. If that's right, I could play with it and see how it behaves. I may have questions though, such as, what in my code would you replace with your code?

  7. #27
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    Now we're getting into deeper waters and I'm having trouble staying afloat.
    the best way to learn is to swim
    if your code works as intended for you, do not change it.
    there will be nothing more optimal than comfort.

    the filter serves no purpose unless you plan to save over a file, or pick a file with a certain extension. So for YOU the filter is pointless.
    customizing the window title is as easy as adding this to this line

    Code:
    strPath = Application.GetSaveAsFilename(strResult, "Excel Files (*.xlsm), *.xlsm", , "INSERT TITLE HERE")
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  8. #28
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    That's awesome. I feel like I'm pushing my luck here, but the more we do, the more I get creative.

    Since we've already asked for a new filename, and I can put that in the title of the SaveAs dialog box, is there any option to remove the File name: and Save as Type: boxes from the dialog so that the user only chooses a folder to save to and either saves or cancels the operation? In other words, I'd like to only show the folders so the user can't write over another file accidentally unless it happens to be the same name. I think a filter would be necessary for that to only show the folders.

    Have I gone too far?

  9. #29
    Board Regular AndyTampa's Avatar
    Join Date
    Aug 2011
    Location
    Brandon, FL
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by BlakeSkate View Post
    I use Application.FileDialog
    it allows things like

    Code:
        With Application.FileDialog(msoFileDialogFilePicker)
            .Title = "Pick a save desination"
            .Filters.Add "Excel Files", "*.xlsm*"
            .AllowMultiSelect = False
            .InitialFileName = ThisWorkbook.Path & "\"
            If Not .Show Then Exit Sub
            wfile = .SelectedItems.Item(1)
            wdiag = InStrRev(wfile, "\")
            wpath = Left(wfile, wdiag)
            wbook = Mid(wfile, wdiag + 1)
        End With
    where you would need to change whats in bold to msoFileDialogSaveAs in order to save a file as opposed to picking a file.
    try this out with its many options if you want more customization
    I tried it out with msoFileDialogSaveAs and it errored out at .Filters.Add "Excel Files", "*.xlsm*". I'm getting Run-time error '438': Object doesn't support this property or method. I commented this line out and the macro will continue, but wants to save the file as an .xlsx file instead. But even if I let it continue to save it as .xlsx, it doesn't save it or doesn't put it in the selected directory.

    It does look similar to the behavior of the macro I got working. Would it offer the option of removing the Save As Type and File Name boxes?

  10. #30
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to save a copy of a workbook with a new name

    Quote Originally Posted by AndyTampa View Post
    Would it offer the option of removing the Save As Type and File Name boxes?
    no? i don't see why you would have used your solution over my initial one on this post if you were not wanting the dialogue boxes?
    to my knowledge you can't restrict the basic functionality of a save as box, just the options it allows like selecting multiple files etc.
    if you were wanting someone to select a folder to save in you could use file picker in the same kind of way we are using strResult

    Code:
    Sub foldR()
        Dim foldR As String
        Dim diaG As FileDialog
        Dim sSelect As String
        
        Set diaG = Application.FileDialog(msoFileDialogFolderPicker)
        With diaG
            .Title = "Select a Save Destionation"
            .AllowMultiSelect = False
            .InitialFileName = Application.DefaultFilePath
            If .Show <> -1 Then GoTo err
            sSelect = .SelectedItems(1)
        End With
        
    err:
        foldR = sSelect
        Set diaG = Nothing
    End Sub
    foldR would be your folder name, just note you would have to add the "/" at the end in addition with strResult to get a valid save
    so it would be something like


    Code:
    Sub foldR()
        Dim newWB As String
        Dim foldR As String
        Dim diaG As FileDialog
        Dim sSelect As String
      
    newWB = InputBox("Enter a save name")
    If newWB <> "0" Then
        Set diaG = Application.FileDialog(msoFileDialogFolderPicker)
        With diaG
            .Title = "Select a Save Destionation"
            .AllowMultiSelect = False
            .InitialFileName = Application.DefaultFilePath
            If .Show <> -1 Then GoTo err
            sSelect = .SelectedItems(1)
        End With
    
    err:
        foldR = sSelect
        Set diaG = Nothing
    
    
    ActiveWorkbook.Sheets.Copy
      With ActiveWorkbook
        .SaveAs foldR & "/" & newWB & Format(Date, "MM-DD-YYYY") & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        .Close False
      End With
    Else
    End If
    
    End Sub
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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