VBA Copy/Paste Worksheet

[MostafaAbdElKader]

Hi All -

Having an issue with copying a worksheet and saving into destination on local drive with the data inside moved with all the equations and data validation - need to copy it only as a read only [No equations, No list] - values only -

On Error Resume Next


strDirname1 = "XYX"
strDefpath1 = "D:\YYY\"


MkDir strDefpath1 & strDirname1
strPathname1 = strDefpath1 & strDirname1 & "\" & MyFileName1




MyFileName1 = "NAME01 "
If Not Right(strPathname1, 1) = "\" Then strPathname1 = strPathname1 & "\"
If Not Right(MyFileName1, 4) = ".xlsx" Then MyFileName1 = MyFileName1 & ".xlsx"


Sheets("SERVICE SUPPORT TIME SHEET").Copy
With ActiveWorkbook
    .SaveAs Filename:= _
        strPathname1 & MyFileName1, _
        FileFormat:=51, _
        CreateBackup:=False
        .Close False




Workbooks.Open Filename:=strPathname1 & ".xlsx"
Workbooks(MyFileName1 & ".xlsx").Activate


          
    Workbooks(MyFileName1 & ".xlsx").Save
 Workbooks(MyFileName1 & ".xlsx").Close SaveChanges:=True


  


End With

 

[JackDanIce]

Welcome to the board. Try adding part in red:

On Error Resume Next
    
    strDirname1 = "XYX"
    strDefpath1 = "D:\YYY\"
    
    MkDir strDefpath1 & strDirname1
    strPathname1 = strDefpath1 & strDirname1 & "\" & MyFileName1
    
    
    MyFileName1 = "NAME01 "
    If Not Right(strPathname1, 1) = "\" Then strPathname1 = strPathname1 & "\"
    If Not Right(MyFileName1, 4) = ".xlsx" Then MyFileName1 = MyFileName1 & ".xlsx"
    
    
    sheets("SERVICE SUPPORT TIME SHEET").Copy
    With ActiveWorkbook
        With ActiveSheet
            .Cells.Values = .Cells.Values
        End With
        .SaveAs Filename:=strPathname1 & MyFileName1, FileFormat:=51, CreateBackup:=False
        .Close False
        Workbooks.Open Filename:=strPathname1 & ".xlsx"
        Workbooks(MyFileName1 & ".xlsx").Close SaveChanges:=True
    End With