Excel VBA

css navigation by Css3Menu.com

Convert Lots of Tabs

Some of our managers were receiving files with up to 50 sheets in each one. They did not want to send the full pack to every salesman.

This utility that runs as part of a menu, asks for a naming convention and splits the workbook accordingly.

Private Sub cmdOK_Click()
    Dim Naming As String, daBooks As Long, daPath As String
    Dim ActingBook As String
    usrTabs2Books.Hide
    
    daPath = ActiveWorkbook.Path		'Get current path
    ActingBook = ActiveWorkbook.Name
    Application.ScreenUpdating = False
    For daBooks = 1 To Workbooks(ActingBook).Worksheets.Count	'Count Sheets
        If optFront = True Then		'Where to put the naming conv
            Naming = txtNamingConvention.Text & "-" & Sheets(daBooks).Name
        Else
            Naming = Sheets(daBooks).Name & "-" & txtNamingConvention.Text
        End If
        Sheets(daBooks).Select
        Sheets(daBooks).Copy		'Copy the sheet to new workbook
        Application.DisplayAlerts = False	'Turn OFF alerts
        ActiveWorkbook.SaveAs Filename:=daPath & "\" & Naming & ".xls", FileFormat:= _
        xlNormal			'Name it
        ActiveWorkbook.Close		'Close new workbook
        Application.DisplayAlerts = True	'Alerts back ON
    Next
    Application.ScreenUpdating = True
    MsgBox "Completed splitting " & daBooks & " sheets to " & daPath, _
        vbInformation, "Progress"
End Sub

Most of the tricks are in the UserForm dialog.

Break into lots of sheets


© 2005-2024

Updated:  01/23/2024 13:34
This page added:  25 December 2005