How to use VBA code to output multiple worksheets to separate workbooks
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to generate report based on criteria |
| How to use Excel VBA code to hide based on criteria |
| How to protect VBA source code from view |
| How to use VBA to turn columns into rows |
| How to turn one operation into a loop in VBA |
| How to create VBA for index and match based on sheet criteria |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.