Export tabs as values issue

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
160
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,127,620
Messages
5,625,910
Members
416,141
Latest member
Bartek9q

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top