Making new EXCEL file with value only by copy from other excel

[tendosai]

Greeting everyone,
I have a bit of complicated manual work thus i wanna make a VBA to handle the task with a click.
I have one master EXCEL file namely "MASTER.xlsm" it contains many sheet with both value and formula to calculate another table for different sheet. Manually, I would have to create a copy to new workbook then copy/paste value only to remove the formula. This took me a while because i have to make 3 to 4 new workbook with no formula.

The macro i try to config should be able to make new 3 new workbooks (different name / different sheet) base on "MASTER.xlsm" with all format and value intact but no formula in there.
I found out about this
worksheet.UsedRange.Value = worksheet.UsedRange.Value

but does this also copy the format as well?

shall i first make sheet copy to new workbook then use above code or maybe there is a way that both copy sheet to new workbook with all format intact but no formular?

[tendosai]

Sub CopyWorksheetValues()    Dim ws As Worksheet
    Dim folderPath As String
    folderPath = Application.ThisWorkbook.Path
    Worksheets(Array("Keep1", "Keep2")).Copy
    ws.Cells.Copy
    ws.Range("A1").PasteSpecial Paste:=xlPasteValues
    ws.Application.CutCopyMode = False
    With ActiveWorkbook
     .SaveAs Filename:=folderPath & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook
     .Close SaveChanges:=False
    End With
End Sub

I try to config this tho it got error of variable. This will make new excel file namely New3.xlsx but the it also copy all the formula too.

[tendosai]

Option Explicit
Sub CopyWorksheetValues()
    Dim ws As Worksheet
    Dim WS_Count As Integer
    Dim I As Integer
    Dim folderPath As String
    folderPath = Application.ThisWorkbook.Path
    Worksheets(Array("Keep1", "Keep2")).Copy
    With ActiveWorkbook
    WS_Count = .Worksheets.Count
        For I = 1 To WS_Count
        .Sheets(I).UsedRange.Value = .Sheets(I).UsedRange.Value
        Next
     .SaveAs Filename:=folderPath & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook
     .Close SaveChanges:=False
    End With
End Sub

Finally i got it works but how can i make it generate 3 excel instead of one

[paulked]

Option Explicit
Sub CopyWorksheetValues()
    Dim ws As Worksheet
    Dim WS_Count As Integer
    Dim I As Integer
    Dim folderPath As String
    folderPath = Application.ThisWorkbook.Path
    Worksheets(Array("Keep1", "Keep2")).Copy
    With ActiveWorkbook
    WS_Count = .Worksheets.Count
        For I = 1 To WS_Count
        .Sheets(I).UsedRange.Value = .Sheets(I).UsedRange.Value
        Next
     .SaveAs Filename:=folderPath & "\New3.xlsx", FileFormat:=xlOpenXMLWorkbook
'Make whatever changes here
     .SaveAs Filename:=folderPath & "\New4.xlsx", FileFormat:=xlOpenXMLWorkbook
'Make even more changes here
     .SaveAs Filename:=folderPath & "\New5.xlsx", FileFormat:=xlOpenXMLWorkbook

     .Close SaveChanges:=False
    End With
End Sub