Move sheets to new workbook and save

[Valentin]

I need a macro to move all sheets except last sheet to new workbook and save the new workbook

 

[Sahak]

Hi Valentin,

Try This:

Sub Delete_Last_Sheet()
    Application.DisplayAlerts = False
    Sheets(Sheets.Count).Delete
    Call SaveFile_WithNewName
End Sub
Sub SaveFile_WithNewName()
    Dim newName As String
    newName = "NewWorkbook"
    ActiveWorkbook.SaveAs Filename:=newName
End Sub

 

[Valentin]

This is not the solution I was looking for. Last sheet may not be removed

 

[Sahak]

I’m sorry if I misunderstand

You say: I need a macro to move all sheets except last sheet to new workbook and save the new workbook

Which in my understanding means: save to the new workbook all sheets, except the last sheet (what my macro does), am I wrong?

 

[Valentin]

I’m sorry if I misunderstand

You say: I need a macro to move all sheets except last sheet to new workbook and save the new workbook

Which in my understanding means: save to the new workbook all sheets, except the last sheet (what my macro does), am I wrong?

Last sheet may not be removed from original file

 

[Valentin]

Last sheet may not be removed from original file

Move sheets not copy

 

[navic]

For this purpose, I used VBA below

Sub MoveSheetFromMasterToClosedWbk()
' Master book is source
' Book1 is destination
Dim mybook As Workbook
Application.ScreenUpdating = False
Set mybook = Workbooks.Open("C:\Temp\Book1.xlsx")
Workbooks("Master.xlsx").Sheets("Sheet1").Move After:=mybook.Worksheets(Sheets.Count) 'Specified Master book and sheet for moving or copying
Workbooks("Master.xlsx").Sheets("Sheet2").Move After:=mybook.Worksheets(Sheets.Count)
Workbooks("Master.xlsx").Sheets("Sheet3").Move After:=mybook.Worksheets(Sheets.Count)
mybook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

Open the master workbook, copy the VBA code into the standard Module and run it.
In the VBA code, change the master workbook name and file extension (*.xlsm) to the opened workbook name, if necessary.

 

[Sahak]

I think we don't understand each other
The original file remains as is, with no changes, the last sheet not removed. The macro creates a new file, which will have exactly the same sheets, except for the last sheet, so we will have 2 files, original & new. How I understand Excel, move sheets or leave sheets as is and save under new name, are the same things, they will have the same formats, formulas &, etc.
I'm sorry, I tried to help.

 

[Valentin]

Moving sheets is not the same as copying sheets.
I think it is possible with "i to sheets count-1" but I don't know the correct syntaxs.

 

[mohadin]

Hi
What about

Sub MoveWs()
Dim wb1, wb2 As Workbook
Dim i, y As Long
Set wb1 = ThisWorkbook
    Set wb2 = Workbooks.Add
    y = 1
    For i = 1 To wb1.Sheets.Count
        If y = 0 Then Exit Sub
        wb1.Sheets(y).Move After:=wb2.Sheets(wb2.Sheets.Count)
        y = wb1.Sheets.Count - 1
    Next
End Sub