Forum Discussion
infinity94
Jun 04, 2021Copper Contributor
how to save one sheet from a workbook in a specific folder with specific name
Hi all, Anyone knows how to automatically save a specific sheet from a workbook "Sheet 2" in a specific folder with a specific name depending on the cell A7 in "Sheet 1" with todays date?
JKPieterse
Jun 04, 2021Silver Contributor
infinity94 Like this:
Sub SaveSheet()
Dim filename As String
filename = Worksheets("Sheet 1").Range("A7").Value & ".xlsx"
Worksheets("Sheet 2").Copy
ActiveWorkbook.SaveAs "c:\YourPathHere\" & filename, xlOpenXMLWorkbook
ActiveWorkbook.Close savechanges:=False
End Sub
- infinity94Jun 04, 2021Copper Contributorthank you for this but what if I have different drop downs in cell A7?
So if in A7 it says Dan I want to save it in one folder
if A7 says Ana I want to save it in a different folder. How would i do that?- JKPieterseJun 04, 2021Silver Contributor
infinity94 You can use this of the path is in -say- A8:
Sub SaveSheet() Dim filename As String Dim filePath As String filename = Worksheets("Sheet 1").Range("A7").Value & ".xlsx" filePath = "c:\FirstPartOfPathHere\" & Worksheets("Sheet 1").Range("A8").Value If Right(filePath, 1) <> "\" Then filePath = filePath & "\" End If Worksheets("Sheet 2").Copy ActiveWorkbook.SaveAs filePath & filename, xlOpenXMLWorkbook ActiveWorkbook.Close savechanges:=False End Sub
- JKPieterseJun 04, 2021Silver ContributorBut you can also use regular cell formulas to build your won path and filename all in one cell and I'm sure you'll be able to figure out to use that in the code sample I gave 🙂