Results 1 to 4 of 4

Thread: Prompt User for Save Location

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Prompt User for Save Location

    Hi

    The code below takes several sheets in a Workbook and saves each sheets as its own Workbook. It saves the workbooks in the same directory that the source wookbook is saved. But Id like to prompt the user once to select a folder where all the workbooks will be saved. How do I do this ?

    Code:
    'Maybe prompt the user here for the folder where the workbooks will be saved ???
    
    
        For i = 1 To 10
        Sheets(i).Copy
        SaveDate = Format(Now(), "dd-mmm-yy  hhmm")
        
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename:=Sheets(1).Name & " Holds Report - " & _
                SaveDate, AccessMode:=xlExclusive, ConflictResolution:=True
            Application.DisplayAlerts = True
            ActiveWorkbook.Close
            
            Workbooks("KPI Generator.xlsm").Activate
            
        Next i

    Thank you in advance

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Prompt User for Save Location

    How about
    Code:
    Sub musical()
       Dim Pth As String
       
       With Application.FileDialog(4)
          .AllowMultiSelect = False
         If .Show = -1 Then Pth = .SelectedItems(1)
       End With
       For i = 1 To 10
          Sheets(i).Copy
          SaveDate = Format(Now(), "dd-mmm-yy  hhmm")
       
           Application.DisplayAlerts = False
           ActiveWorkbook.SaveAs filename:=Pth & "\" & Sheets(1).Name & " Holds Report - " & _
               SaveDate, AccessMode:=xlExclusive, ConflictResolution:=True
           Application.DisplayAlerts = True
           ActiveWorkbook.Close
           
           Workbooks("KPI Generator.xlsm").Activate
           
       Next i
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Prompt User for Save Location

    Thank you Fluff - that worked great

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Prompt User for Save Location

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •