VBA copy and paste as values and save as new file based on table list

[Beginner001]

Hi, is it possible to copy a specific sheet and paste it to qa new sheet as values then save it to designated folder indicated in the cell?

so im trying to have a table reference/summary sheet of all the tabs/sheets that needs to be saved and the corresponding folder to save it.

is it possible to make it loop like if i need to add a new tab i won't need to add the tab name in the vba code or something.

 

[Beginner001]

i think i was able to solve by patching codes from other threads

Sub SaveSheetsToFiles()
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False
   
   
    Dim SummarySheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim SummaryMsg As String
       
       
    'Set the summary sheet
    Set SummarySheet = ThisWorkbook.Sheets("Summary")
   
    'Find the last row with data in column A of the summary sheet
    lastRow = SummarySheet.Cells(SummarySheet.Rows.Count, "C").End(xlUp).Row
   
    'Loop through each row in the summary sheet
    For i = 2 To lastRow 'Assuming row 1 is header
        Dim sheetName As String
        Dim fileName As String
        Dim folderPath As String
       
        'Get the sheet name and folder path from the summary sheet
        sheetName = SummarySheet.Cells(i, "C").Value
        folderPath = SummarySheet.Cells(i, "E").Value
        fileName = SummarySheet.Cells(i, "D").Value
       
        'Check if the sheet exists in the workbook
        If SheetExists(sheetName) Then
            'Copy the sheet to a new workbook
            ThisWorkbook.Sheets(sheetName).Copy
           
            'Copy and paste values only
            With ActiveSheet.UsedRange
                .Value = .Value
            End With
           
            'Save the new workbook with the specified name and path
            ActiveWorkbook.saveas folderPath & "\" & fileName & ".xlsx"
                       
            ' Add sheet name to summary message
            SummaryMsg = SummaryMsg & sheetName & ">" & ActiveWorkbook.Name & " saved to " & folderPath & vbCrLf
               
            'Close the new workbook without saving changes to it
            ActiveWorkbook.Close False
               
        End If
  
 
    Next i
 
    ' Display summary message in a message box
    MsgBox "Sheets saved successfully:" & vbCrLf & SummaryMsg, vbInformation
 
End Sub
 
Function SheetExists(sheetName As String) As Boolean
    On Error Resume Next
    SheetExists = Not Sheets(sheetName) Is Nothing
    On Error GoTo 0
End Function

 

[Peter_SSs]

Glad you got it solved. For the future though, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Beginner001]

Thank you! i will keep this in my notes!

 

[Peter_SSs]

Cheers.