Consulting

Results 1 to 3 of 3

Thread: Save-As name based on cell value

  1. #1
    VBAX Newbie
    Joined
    Aug 2010
    Posts
    2
    Location

    Save-As name based on cell value

    Can someone please help

    I am looking for a code that will:

    1) Bring up save-as popup
    2) Worksheet to be saved as name in cell (A1)
    and either
    path to be chosen (preferable)
    or
    to desktop.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Why not just bring up the folder browser and then save in that folder as A1?

    Option Explicit
    
    Sub SaveA1()
        Dim sFolder As String, sFile As String
        
        With Application.FileDialog(msoFileDialogFolderPicker)
            If .Show = -1 Then ' if OK is pressed
                sFolder = .SelectedItems(1)
            Else
                Exit Sub
            End If
        End With
        
        If Len(sFolder) > 0 Then 'a file was chosen
            sFile = sFolder & Application.PathSeparator & ActiveSheet.Range("A1").Value
            
            If UCase(sFile) = UCase(ThisWorkbook.FullName) Then
                MsgBox "Can't save with same name as this workbook"
                Exit Sub
            End If
            
            On Error Resume Next
            Application.DisplayAlerts = False
            Kill sFile
            Application.DisplayAlerts = True
            On Error GoTo 0
            
            ThisWorkbook.SaveAs sFile
            
            MsgBox "This workbook saved as " & vbCrLf & vbCrLf & sFile
            
            ThisWorkbook.Close True
        End If
    End Sub
    No error checking
    Last edited by Paul_Hossler; 01-10-2022 at 05:04 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3

Posting Permissions

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