PDA

View Full Version : [SOLVED] Prompt user to select file path and inserting name with vba



arron
06-16-2017, 06:42 AM
I've got a fairly simple macro that selects data from a specified cell and saves the work sheet with the value of the cell being the name. my trouble is that its just saving files directly to the documents folder. i was wondering how i might go about inserting a prompt to choose a destination, while retaining the auto naming function. furthermore i was hoping to insert the value from a second cell into the name, ex. ("I12") to ("I12" + "C15"). I know this is going to be frustratingly simple and any help is greatly appreciated.



Sub CommandButton4_Click()
Dim Fname As String, ws As Worksheet
Fname = Sheets("Sheet2").Range("I12").Value
Sheets(Array("Sheet2")).Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Value = .Value
End With
Next ws
With ActiveWorkbook
.SaveAs Filename:=Fname
.Close
End With
End Sub

mdmackillop
06-16-2017, 07:26 AM
Sub CommandButton4_Click()
Dim Fname As String, ws As Worksheet, FPath As String
FPath = Application.InputBox("Select cell", , , , , , , 8)
Fname = Sheets("Sheet2").Range("I12").Value
Sheets(Array("Sheet2")).Copy
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
.Value = .Value
End With
Next ws
With ActiveWorkbook
.SaveAs Filename:=FPath & Fname
.Close
End With
End Sub

arron
06-16-2017, 07:47 AM
Hi mdmackillop,
thanks for the reply, i think my post may have been a touch confusing. the data will always be in the same cell so Id like to have it automatically pull the text from cells I12 and C15 for the file name. when I spoke about user input I meant that the I need the user to choose a folder in which to save the file

mdmackillop
06-17-2017, 12:20 PM
Try this (http://www.vbaexpress.com/kb/getarticle.php?kb_id=284)

arron
06-20-2017, 07:01 AM
this is great. thanks for the help, ill be able to use this with a ton of macros moving forward