Forum Discussion

infinity94's avatar
infinity94
Copper Contributor
Jun 04, 2021

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? 
excel
Macros and VBA
JKPieterse's avatar
JKPieterse
Silver Contributor
Jun 04, 2021

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
infinity94's avatar
infinity94
Copper Contributor
Jun 04, 2021
thank 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?
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jun 04, 2021

    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

     

     

     

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Jun 04, 2021
      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's avatar
        infinity94
        Copper Contributor
        Jun 08, 2021
        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

Resources