Macro to export all sheets from BTE ME 80214 to last sheet and save as Sales Cheque advices in C:\My documents

[howard]

I have the following code below export all sheets from BTE ME 80214 to last sheet and save as Sales Cheque advices in C:\My documents

Hover not all the sheets are being exported


It would be appreciated if someone could kinfdly check and amend my code

 Sub ExportSheetsAsWorkbook()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim fileName As String
    Dim filePath As String
    Dim wb As Workbook
    Dim i As Integer
    
    ' Set the file path and name where you want to save the new workbook
    filePath = "C:\My Documents\"
    fileName = "Sales Cheque advices.xlsx"
    
    ' Create a new workbook
    Set wb = Workbooks.Add
    
    ' Loop through all sheets from BTE ME 80214 to the last sheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Index >= ThisWorkbook.Worksheets("BTE ME 80214").Index Then
            
            ' Copy the sheet to the new workbook
            ws.Copy After:=wb.Sheets(wb.Sheets.Count)
            
            ' Rename the sheet in the new workbook
            wb.Sheets(wb.Sheets.Count).name = ws.name
            
        End If
    Next ws
    
  ' Save the new workbook with the specified file path and name
    wb.SaveAs fileName:=filePath & fileName
    
    ' Close the new workbook
    wb.Close
    
End Sub

 

[mumps]

Try:

Sub ExportSheetsAsWorkbook()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, fileName As String, filePath As String, wb As Workbook, i As Integer, srcWB As Workbook
    Set srcWB = ThisWorkbook
    filePath = "C:\My Documents\"
    fileName = "Sales Cheque advices.xlsx"
    Workbooks.Add
    For Each ws In srcWB.Sheets
        If ws.Index >= srcWB.Worksheets("BTE ME 80214").Index Then
            ws.Copy After:=Sheets(Sheets.Count)
        End If
    Next ws
    With ActiveWorkbook
        .SaveAs fileName:=filePath & fileName
        .Close False
    End With
    Application.ScreenUpdating = True
End Sub

 

[howard]

Many Thanls for the help mumps

Your code works perfectly

 

[mumps]

You are very welcome.