Save as Multiple sheets as in separate workbook

[haribole]

I have workbook containing above 40 worksheets. I want to save only 30 worksheets as excel in separate workbook. Please any body can help !!!

Dim wb As WorkbookDim ws As Workbook
Dim DateFrom As String, DateTo As String, UserName As String
Dim MySheetName() As String
Dim i As Integer
DateFrom = Worksheets(1).Cells(1, 1)
DateTo = Worksheets(1).Cells(2, 1)
UserName = InputBox("Enter Password")
If UserName = "****" Then

'Here I need the code


ReDim MySheetName(Sheets(DateFrom).Index To Sheets(DateTo).Index) As String
    For i = Sheets(DateFrom).Index To Sheets(DateTo).Index Step 1
    MySheetName(i) = Worksheets(i).Name
    Next i
       For i = Sheets(DateFrom).Index To Sheets(DateTo).Index Step 1
       Application.DisplayAlerts = False
       Sheets(MySheetName(i)).Delete
       Application.DisplayAlerts = True
       Next i
Else
Msgbox "Incorrect Password"
End If

 

[njimack]

You haven't provided much info - how do you determine which sheets to save? Where will they be saved? How will their filename be derived?

The below (untested!) code shows you how to loop through every sheet in a file and save them to a new workbook

Sub Macro1()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    With ActiveWorkbook
        .SaveAs "Filename"
        .Close
    End With
Next ws
End Sub

 

[Fluff]

How about

If UserName = "****" Then
ReDim MySheetName(Sheets(DateFrom).Index To Sheets(DateTo).Index) As String
    For i = Sheets(DateFrom).Index To Sheets(DateTo).Index Step 1
      MySheetName(i) = Worksheets(i).Name
    Next i
    Application.DisplayAlerts = False
    Sheets(MySheetName).Move
    Application.DisplayAlerts = True
Else

 

[haribole]

Thanks !!!
1) DateFrom (from the sheet) to DateTo ( to the sheet)
2) MyFolder = "D:\NewCPCReport"
3) File name will derive from InputBox ("Enter File Name")

 

[haribole]

Thanks !!! for your comments:
1) DateFrom (from sheet) to DateTo (to sheet)
2) MyFolder = "D:\NewCPCReport"
3) File name will be derived from InputBox ("Enter File Name") (as excel normal)