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

6 Replies

  • 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

         

         

         

Resources