Save sheet as a new values only workbook to a specified location

[Scruffy Space Gardener]

Hi Experts,

I am trying to piece together a VBA code for a command button to:

1) save a copy of a sheet into a new workbook (source sheet currently located in a collaborative workbook which I would like to preserve)
2) save it as a values only copy into a specified folder (take away the formatting)
3) name the new workbook as a combination 3 other cells on the source sheet [a5 & b5 & c5]

I'd greatly appreciate the help.

Thank you!

 

[JLGWhiz]

You will need to replase "ThisWorkbook.Path" in the code with the specific folder path that you want to save the file to. When starting a thread where a file needs to be saved to other than the default directory, you should spell out what that directory path is in the original post to save time and effort of having to do it after the fact.

Sub t()
Dim sh As Worksheet, wb As Workbook, specFolder As String
specFolder = ThisWorkbook.Path 'Change to path for specific folder
Set sh = ActiveSheet
Set wb = Workbooks.Add
sh.UsedRange.Copy
wb.Sheets(1).Range("A1").PasteSpecial xlPasteValues
wb.SaveAs specFolder & "\" & sh.Range("A5") & sh.Range("B5") & sh.Range("C5")
wb.Close False
End Sub