Help Extract from a Excle File Multiple Sheets with Specific Column Range

Dear all,

I need your help

I have a File, inside this file there are n sheets all formatted in same way but each sheet has different name.

I need to extract from the file all this sheet with the same specific range of column.

For example:
File RESULT
SHEETS 1 2 3 4 5
Column range C to D and Y to Z

I need to create 5 new file, each of them called with the same name of the sheets so 1 2 3 4 5 and on each of them I must find the data from the range column mentioned above...


now I am using this macro, but it create just the files based on the n sheets of master file

Sub Splitbook()
'Updateby20140612
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
    xWs.Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xlsx"
    Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

and I want to combine it with this macro that copy specific columns for each n sheets in the n files created

Public Sub extractCol()
     Set range1 = Range("C:D, Y:Z")
     range1.Copy
     Set newbook = Workbooks.Add
     ActiveCell.PasteSpecial Paste:=xlPasteValues
 End Sub

I wish I was clear, I need to combine them please...


also my reports for each sheets have merged cells, I need also to remove th emerge in order to copy the columns.


thanks a lot

lorenzo