How to split Excel book with multiple sheets to single files

This guide shows how to split Excel workbook with multiple sheets into single files. For most compatibility, for ".xls" files (Excel 97-2003) SplitSheetsToFiles macros will create single ".xls" files, containing only 1 sheet from source Excel book, the same macro's behavior will be for ".xlsx", ".xlsm", ".xlsb" files (Excel 2007, 2010, 2013).

1. Please download SplitSheetsToFiles archive and unzip it to HDD.

2. Import SplitSheetsToFiles.bas Module into your Excel book (Read How-To). In our SplitSheetsToFiles.xlsm sample workbook contains 3 sheets:

SplitSheetsToFiles_01_3_Sheets_Free-Excel-VBA.Blogspot.com




3. Please return to sheet in your Excel you need to split into single files.

4. Please Save your file into HDD as Macro-Enabled Workbook (.xlsm), if you use Excel 2007, 2010, 2013, etc, or  Excel 97-2003 Workbook (.xls).

5. Press "ALT+F8", select "SplitSheetsToFiles" in "Macro" window and press "Run" button.

6. In a few moments all sheets from your source Excel workbook will are saved into single files at the same folder, which will contains only 1 sheet in each. Here the listing for our SplitSheetsToFiles.xlsm sample workbook

SplitSheetsToFiles_02_File_Listing_After_Splitting_Free-Excel-VBA.Blogspot.com

 SplitSheetsToFiles.bas macros features, limitations and error-protections:
a) Macros will not work until you save file into your HDD. If you open existing Excel workbook with "xlsx" extension, import SplitSheetsToFiles.bas and run it, macros will work.
b) Macros creates single file's extension based on source file extension - for ".xlsx" will are created ".xlsx" files, for "xlsm" - "xlsm", for "xlsb" - "xlsb", for "xls" - "xls", even you use Excel 2007, 2010, 2013, etc.
c) Macros will not erase existing workbook files with same names, it will auto-numerate them until a non-existing filename to save will be found. During auto-numeration macros will not exceed 31 symbols maximum length for sheet name.
d) For Excel 2007, 2010, 2013 users in case of Run-time error '1004': "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use Copy and Paste commands to insert it into the sheets of another workbook." please do:
Select "File" tab, -> "Options" -> "Save" -> "Save workbooks" section -> "Save files in this format:" -> "Excel Workbook (*.xlsx)" or "Excel Macro-Enabled Workbook (*.xlsm)"
Then Press OK button. Restart the Excel and repeat operation from step 2.

How to make multiple copies of Excel sheet in automatic mode

In this guide will be shown how to make multiple copies of Excel sheet in automatic mode. Two or three sheets could be copied manually very fast, but what about 15 or 50 sheets? Our AutoSheetsCopy macros will make it easily and very fast. It works with any Excel books. Maximum sheets to be added in your book - 1024.

1. Please download AutoSheetsCopy ZIP archive and unzip it to HDD.

2. Import AutoSheetsCopyForm.frm Form file into your Excel book (Read How-To)

3. Import AutoSheetsCopyModule.bas Module file into your Excel book (Read How-To)

4. Please return to sheet in your Excel you need to copy, press "ALT+F8", select "AutoSheetsCopy" in "Macro" window and press "Run" button. Initial User Form with "Auto Sheets Copy" caption will appear.

AutoSheetsCopy_01_Initial_Window_Free-Excel-VBA.Blogspot.com


5. Select (up to 1024) number of copies, then press "Copy" button

AutoSheetsCopy_02_Select_Copies_Free-Excel-VBA.Blogspot.com

6. In a few moments the sheet you have selected will be copied. By default, new sheets will be named as "Sheet1_2", "Sheet1_3", etc. Maximum length of Excel sheet's name is 31.

AutoSheetsCopy_03_Sheet_Copied_Free-Excel-VBA.Blogspot.com
 




How to import and run Excel Macros in .bas format

This guides describes how to import and run Excel Macros in .bas format. Macros importing will work with open Workbook.
1. Please press "ALT+F11" key combination. Microsoft Visual Basic for Applications window will appear.
2. Select from "File" menu - "Import File... (CTRL+M)".

Import_Module_01_Free-Excel-VBA.Blogspot.com

3. Select TestModule.bas from your hard drive and press "Open" button. New "Module1" will be shown in VBAProject window

Import_Module_02_View_Code_F7_Key_Free-Excel-VBA.Blogspot.com


4. To View Macros code right click and select "View Code". "TestModule" VBA macros code will be shown

Import_Module_03_View_Code_Window_Free-Excel-VBA.Blogspot.com


5. Please return to Sheet1 window, press "ALT+F8", select "TestModule" in "Macro" window and press "Run" button.

ALT_F8_Eng_Free-Excel-VBA.Blogspot.com


Your VBA macros in .bas format will be executed. These are results of  TestModule.bas macros running. You can download Book1 XLS file with TestModule.

Import_Module_04_Macros_Result_Free-Excel-VBA.Blogspot.com


Video guide:



Passwords Generator

For some purposes IT specialists, for example, could be needed to obtain base with passwords to use them in their networks, domains, work groups, etc. So, we present Excel based password generator powered by VBA macro.

Passwords Generator Free-Excel-VBA.Blogspot.com

Password Generator user need to change quantity (15 as shown in a screen), password length (from 1 to 255), and symbols to use in your passwords listings. Then Press "Alt+F8", select "Passwords_Generator" macro and then press "Run" button. Maximum passwords: 65530, maximum password length: 255 symbols. Default symbols:
!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~
You can change, add, modify symbols string - your passwords will be generated with them only.
If there will are some errors, macro will generate 25 passwords with 8 symbols in each.
For blank symbols string macro will use "0123456789" string.
There are no hidden activities, such as password "stealing", sending, accumulating. You can view source code by pressing "Alt+F11" to check it. 
Macro compatibility: MS Excel 97, 2000, 2003, 2007, 2010, 2013

Download Excel Passwords Generator [41 KBytes]

Video tutorial: