How to use VBA code Output multiple worksheets to separate workbooks

How to use VBA code to output multiple worksheets to separate workbooks

Requirement:

The user's aim is to output approximately 30 worksheets, to seperate workbooks. Frequently, some of those workseets contain no data from row A2 and down. The user wants to stop the macros from generating, what are essentially blank workbooks?

Row A1 will always have a header row, hence why it would need to identify that row A2 is blank.

Solution:

Code:

For Each xWs In xWb.Worksheets
    If Application.CountA(xWs.Rows(2)) > 0 Then
        xWs.Copy
        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            Select Case xWb.FileFormat
                Case 51:
                    FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If Application.ActiveWorkbook.HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56:
                    FileExtStr = ".xls": FileFormatNum = 56
                Case Else:
                    FileExtStr = ".xlsb": FileFormatNum = 50
            End Select
        End If
        xFile = FolderName & "\" & Application.ActiveWorkbook.Sheets(1).Name & FileExtStr
        Application.ActiveWorkbook.SaveAs xFile, FileFormat:=FileFormatNum
        Application.ActiveWorkbook.Close False
    End If
Next

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.