VBA to copy and paste worksheets in new file as values not working properly

[sashazaliz]

The following code below is supposed to copy and paste values 3 specific worksheets within my workbook into a new file within the same directory. Everything works like a charm except the cells in bottom portion of the copied worksheets contain #NAME? instead of the actual pasted values. I suspect this is happening because the bottom portion of those worksheets are all user defined functions instead of simple formulas or references. Are there any tweaks I can make to the code in order to avoid this and just produce values while maintaining formats just the top portion. Thank you!

Option Explicit
 
Sub PasteValueSheets()
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet
     
    
     
    With Application
        .ScreenUpdating = False
         
        
        On Error GoTo ErrCatcher
        Sheets(Array("Output RD 50006-50", "Output RD 50007-50", "Output RD 50009-50", "Output RD 50001-50")).Copy
        On Error GoTo 0
         
        
        For Each ws In ActiveWorkbook.Worksheets
            ws.Cells.Copy
            ws.[A1].PasteSpecial Paste:=xlValues
            ws.Cells.Hyperlinks.Delete
            Application.CutCopyMode = False
            Cells(1, 1).Select
            ws.Activate
        Next ws
        Cells(1, 1).Select
         
        NewName = "OutputPV"
         
        
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & Format(Date, "mmddyyyy") & ".xlsx"
        ActiveWorkbook.Close SaveChanges:=False
         
        .ScreenUpdating = True
    End With
    Exit Sub
     
ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub

 

[Ralajer]

The below differs from your version in that it copies each sheets formatting and values from the source workbook to the new workbook one sheet at a time. It slightly more complicated because of that but it avoids the user defined function issues.

Sub PasteValueSheets2()
    Dim NewName As String
    Dim wsNames As Variant
    Dim i As Integer
    Dim countWS As Integer
    Dim s As Variant
    Dim ws As Worksheet
    Dim sourceWB As Workbook
    Dim destWB As Workbook
    
    Set sourceWB = ThisWorkbook
    
    wsNames = Array("Output RD 50006-50", "Output RD 50007-50", _
                    "Output RD 50009-50", "Output RD 50001-50")
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    '// Verifies that all worksheets exist
    On Error GoTo ErrCatcher
    For i = LBound(wsNames) To UBound(wsNames)
        If Sheets(wsNames(i)).Name <> "" Then i = i
    Next i
    On Error GoTo 0
    
    Set destWB = Workbooks.Add
    
    countWS = destWB.Sheets.Count
    '// Copies the workbooks format and values from the source
    For Each s In wsNames
        Set ws = destWB.Sheets.Add(After:=Sheets(destWB.Worksheets.Count))
        sourceWB.Sheets(s).Cells.Copy
        
        ws.Cells(1, 1).PasteSpecial Paste:=xlPasteFormats, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        ws.Cells(1, 1).PasteSpecial Paste:=xlValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                
        ws.Cells.Hyperlinks.Delete
        ws.Name = s
        Application.CutCopyMode = False
    Next s
    
    '// Delete worksheets that existed when new workbook was created
    For i = countWS To 1 Step -1
        destWB.Sheets(i).Delete
    Next i
   
    NewName = "OutputPV"
       
    destWB.SaveAs ThisWorkbook.Path & "\" & NewName & Format(Date, "mmddyyyy") & ".xlsx", xlOpenXMLWorkbook
    destWB.Close SaveChanges:=False
    
ErrCatcher:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    Exit Sub
    
    MsgBox "Specified sheets do not exist within this workbook"
End Sub