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
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