Export tabs as values issue

[ardykav]

Hi,

I am looking to edit the below code so it works properly. Currently it exports all of the tabs in the named range MLS. All of the tabs in this range are heavily formulated and for some reason when it exports them it leaves out any cells that have formulas in it.

Also I was wondering what is the best way of only exporting the data from Column A-P in each tab.

thanks in advance

Sub exportas()
Dim ws As Worksheet
Dim nm As Name
Dim NewName As String
Dim arrSheets As Variant

    If MsgBox("This will copy sheets to a new workbook" _
              , vbYesNo, "Product Exporter") = vbNo Then Exit Sub

    arrSheets = Range("MLS").Value
    
    arrSheets = Application.Transpose(arrSheets)
    
    With Application
        .ScreenUpdating = False

        '       Copy specific sheets
        On Error GoTo ErrCatcher
    
          Sheets(arrSheets).Copy
        On Error GoTo 0

        '       Paste sheets as values
        '       Remove External Links, Hyperlinks and hard-code formulas
        '       Make sure A1 is selected on all sheets
        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

        '       Remove named ranges
        For Each nm In ActiveWorkbook.Names

        Next nm

l
        ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & "Exported File.xls"
    
        ActiveWorkbook.Close SaveChanges:=False

        .ScreenUpdating = True
        
        MsgBox "File Exported"
        
    End With
    
    Exit Sub

ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
End Sub