XL copy sheets and save them as value

20210210 1411 

 

codes

Sub zSaveAsNewExcelFile()

zpath = ActiveWorkbook.Path
zFile = zpath & "\_investment proposal.xlsx"

zcur = ActiveWorkbook.Name
Workbooks.Add
znew = ActiveWorkbook.Name

Workbooks(zcur).Activate
znmb_report = 0
For i = 1 To ActiveWorkbook.Sheets.Count
  If Left(Worksheets(i).Name, 6) = "report" Then
   znmb_report = znmb_report + 1
  End If
Next i

For i = 1 To znmb_report
  zsheet = "report" & i
  Workbooks(zcur).Activate
  Workbooks(zcur).Sheets(zsheet).Select
  'Selection.Copy
  Application.CutCopyMode = False
  zNmbsheet = Workbooks(znew).Sheets.Count
  Sheets(zsheet).Copy before:=Workbooks(znew).Sheets(zNmbsheet)
  Workbooks(znew).Activate
  Workbooks(znew).Sheets(zsheet).UsedRange.Select
  'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
  Selection.Copy
  Range("A1").Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
Next i
Workbooks(znew).SaveAs Filename:=zFile
Workbooks(zcur).Activate
Workbooks(zcur).Sheets("report1").Select
    
End Sub