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
But 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 🙂
infinity94
Jun 08, 2021Copper Contributor
Thanks a lot for your help!
Actually I do have one more question... trying to add the date to the file name.
added A9=(today)
and then changed code slightly but it just opens up the file with 2021 as name
Sub SaveSheet()
Dim filename As String
Dim filePath As String
filename = Worksheets("Sheet 1").Range("A7").Value & Range ("A9").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
Actually I do have one more question... trying to add the date to the file name.
added A9=(today)
and then changed code slightly but it just opens up the file with 2021 as name
Sub SaveSheet()
Dim filename As String
Dim filePath As String
filename = Worksheets("Sheet 1").Range("A7").Value & Range ("A9").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 08, 2021Silver ContributorDoesn't that work? I'm that case try adding worksheet("Sheet 1") in front of it.