Copy sheet to a new workbook with name of the sheet cell name-CodePudding

I just cannot figure out how to add a name of my copied sheet to a new workbook. What im looking for is the name of the sheet "Activites" cell A11 to register in the folder. As this sheet with data needs to be saved every week, how can i add a cell reference to the saved sheet ? (if not error appears saying this file already exists"

Sub Export_Activities()

Dim worksheet_list As Variant, worksheet_name As Variant Dim new_workbook As Workbook Dim saved_folder As String

saved_folder = "C:\Users\Maz\Desktop\DIMSO\Archives_Activites" worksheet_list = Array("Activites")

For Each worksheet_name In worksheet_list On Error Resume Next

Set new_workbook = Workbooks.Add

ThisWorkbook.Worksheets(worksheet_name).Copy new_workbook.Worksheets(1)

new_workbook.SaveAs saved_folder & worksheet_name & ".xlsx", FileFormat:=51
new_workbook.Close False

Next worksheet_name

MsgBox "L'exportation est complète, N'oubliez pas de renommer la feuille exportée.", vbInformation

End Sub

CodePudding user response：

This should save the workbook with the Worksheet name the value of the Cell A11:

Sub Export_Activities()
    Dim worksheet_list As Variant, worksheet_name As Variant
    Dim new_workbook As Workbook
    Dim saved_folder As String
    Dim workbookname As String '<----- Added line.
    
    saved_folder = "C:\Users\Maz\Desktop\DIMSO\Archives_Activites"
    worksheet_list = Array("Activites")
    
    For Each worksheet_name In worksheet_list
        On Error Resume Next
    
        Set new_workbook = Workbooks.Add
    
        ThisWorkbook.Worksheets(worksheet_name).Copy new_workbook.Worksheets(1)
        
        workbookname = worksheet_name & ThisWorkbook.Worksheets(worksheet_name).Range("A11").Value '<----- Added line.
        
        new_workbook.SaveAs saved_folder & workbookname & ".xlsx", FileFormat:=51 'worksheet_name changed for workbookname
        new_workbook.Close False
    Next worksheet_name

    MsgBox "L'exportation est complète, N'oubliez pas de renommer la feuille exportée.", vbInformation

End Sub