Export specific sheets as New WBK Each

[adeel1]

Hello All

I have excel file save in 97-2003 format, there is several sheets in it I just want to export some of sheets as new wbk each.
Sheets are Month data, Contracts, Expires (which want to export)
New wbk names will be respectively, Dec Hold Data, Under Process, Deleted
Want to save C:\Users\Adeel1\Desktop\in mail\
If already, files exist with same name than auto replace the files!
My sheet has formulas please export as values.


Adeel

[HansV]

Here is a macro:

Code: Select all

Sub ExportSheets()
    Const strPath = "C:\Users\Adeel1\Desktop\in mail\"
    Dim arrSheets
    Dim arrFiles
    Dim i As Long
    Dim wbkS As Workbook
    Dim wshS As Worksheet
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    arrSheets = Array("Month data", "Contracts", "Expires")
    arrFiles = Array("Dec Hold Data", "Under Process", "Deleted")
    Set wbkS = ActiveWorkbook
    For i = LBound(arrSheets) To UBound(arrSheets)
        Set wshS = wbkS.Worksheets(arrSheets(i))
        wshS.Copy
        Set wbkT = ActiveWorkbook
        Set wshT = wbkT.Worksheets(1)
        With wshT.UsedRange
            .Value = .Value
        End With
        wbkT.SaveAs Filename:=strPath & arrFiles(i) & ".xls", FileFormat:=xlExcel8
        wbkT.Close SaveChanges:=False
    Next i
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

[adeel1]

thank you soo much sir for your kind help.

[adeel1]

sorry for in conveyance, i encountered one issue that when i change the name to "Activation F/M Dec'19" ,"Process F/M Dec'19" ,"Deleted F/M Dec'19" for new WBk's code didn't work when i debug
its bring me here

Code: Select all

wbkT.SaveAs Filename:=strPath & arrFiles(i) & ".xls", FileFormat:=xlExcel8

Adeel

[HansV]

A file name cannot contain a / \ : ? * " < > or |
So change the names to "Activation FM Dec'19" or "Activation F_M Dec'19" etc.

[adeel1]

thank you for this, its working now

i changed this line although its working, just want to confirm is it correct as i found this by google.

Code: Select all

& ".xlsx", FileFormat:=xlOpenXMLWorkbook

adeel

[HansV]

That is correct if you want to save as a .xlsx workbook.

[adeel1]

Big thanks for your time and help.

Adeel