How to count sheets is a numeric way regardless of name and the operate as in copy or move-CodePudding

The code and its purpose is that (code below, sub ed) to look at the window with workbook/file name being 'PR11_P3.xlsm', and then count to the last sheet, and select it. Move it (not copy) into a new file and save this to a path/destination. This works as intended.

I'm having error ofc when the is no last sheet which is my goal with this question I'm posting now, best case scenario would be (I think to do a IF) if there is more than one sheet, execute the cod as is, but if and when there is only one sheet or more than 2 sheet's, then copy current sheet rather then move, to the new file and save as, close file.

Any suggestions? basically I want to make sure that there is no scenario where code outputs error cause of sheet count and since the name of the second sheet is always different then this is my only logical approach, but I think is very messy and as mentioned before it just doesn't work - I need expert guidance.

Appreciate the help!

My code

Sub ED() 'Export last sheet into new file in the background, save as and close

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

        Windows("PR11_P3.xlsm").Activate
        Sheets(Sheets.Count).Select
        Sheets(Sheets.Count).Move
        ActiveWorkbook.SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

My code as I think it should be and well I'm not doing such a good job here since there are errors and it dose not run properly.

Sub ED_IF() 

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
If Sheets(Sheets.Count) =< 3 Then 'not more than two sheets
Windows("PR11_P3.xlsm").Activate
Sheets(Sheets.Count).Select
Sheets(Sheets.Count).Move
ActiveWorkbook.SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Else
If Sheets(Sheets.Count) = 1 or => 2 Then 'basically if there is only one sheet or more than 2 sheets, so it can be any number above 2
Windows("PR11_P3.xlsm").Activate
Sheets(Sheets.Count).Copy
ActiveWorkbook.SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End If
End If

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

CodePudding user response：

Something like this:

Sub ED() 'Export last sheet into new file in the background, save as and close
    Dim wb As Workbook, ws As Worksheet, wbNew As Workbook, nSheets As Long
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Set wb = Workbooks("PR11_P3.xlsm")   'or ThisWorkbook?
    nSheets = wb.Worksheets.Count
    Set ws = wb.Worksheets(nSheets)  'sheet to copy/move
    
    With Workbooks.Add(xlWBATWorksheet) 'add a new workbook with 1 sheet
        Select Case nSheets
            Case 1: ws.Copy after:=.Sheets(1)
            Case Else: ws.Move after:=.Sheets(1)
        End Select ' fixed
        .Sheets(1).Delete 'remove the empty sheet
        .SaveAs Filename:="C:\Temp\PR\Export\Bok1.xlsx", _
             FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        .Close
    End With

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub