Wednesday, 2 August 2017

How to Split Each Excel Sheet into Separate Files-Easy (Excel 2003-2016)

Sometime we deal with typical tasks, specially in MS Excel; its title hard to understand module and codes in excel, so the topic is how to split each excel worksheet into separate file without using any software or online, here I will shows you easy steps to do so..

1.       Open Excel File and press “Alt+F11”


2.       Click on “Insert” than click on “Module”


3.       Copy & paste below code:

            Sub Splitbook()
            MyPath = ThisWorkbook.Path
            For Each sht In ThisWorkbook.Sheets
            sht.Copy
            ActiveSheet.Cells.Copy
            ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
            ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
            ActiveWorkbook.SaveAs _
            Filename:=MyPath & "\" & sht.Name & ".xls"
            ActiveWorkbook.Close savechanges:=False
            Next sht
            End Sub   
         
          Click on “Run” icon

That’s it your each worksheet will be converted into separate excel file


Watch video tutorial… Please like, share & subscribe 

10 comments:

Unknown said...

Hi Dude
Thanks for your code. It works fine. can you help me to alter the code where i can save the sheets to folder from Sheet 5.I have some data from sheet 1-4 which i do not want to save to folder. Also can you help me with emailing the sheets as per list to specified recipients.

Unknown said...

Hi Dude
Thanks for your code. It works fine. can you help me to alter the code where i can save the sheets to folder from Sheet 5.I have some data from sheet 1-4 which i do not want to save to folder. Also can you help me with emailing the sheets as per list to specified recipients.

Account Name of the saved file Email ID to be sent based on Account #
80000 PTNB 123@gmail.com
80004 RB-AR 1233@gmail.com
80064 PTDE dfg@gmail.com
80068 RBSI 555@gmail.com
80069 RB-AR 556@gmail.com
80116 BEG Siva@gmail.com
80138 RBJP Radha@gmail.com
80189 RB-AR Muthu@gmail.com
80191 GBAA Rak@gmail.com
80199 RBTY Sat@gmail.com
80231 RBTY Sse@gmail.com
80369 GBAA DDD@gmail.com
80377 PTDE 789@gmail.com
80380 GBAA DDE@gmail.com
80382 STPH eee@gmail.com
80384 RBSI dfa@gmail.com
80402 RB-AR wer@gmail.com
80403 RBSI 852@gmail.com


I have saved this database in a separate sheet "MasterData". After the sheets are saved to the folder. I want to send the email automatically to the list attaching the individual sheets.

Please help me..

Unknown said...

How do I get the the files it saved for each of my tabs to pull the tab formatting. I have colored headers and body rows, but it saves without the color formatting.

Unniyettan said...

The code works perfectly. But the splitted workbooks have been saved in xlsx format, although it has a file extension xls.This prompts to an error message while open the file. please rectify this also

Partha said...

Hello
I am very grateful to you for your code which works fantastic on an Excel file. Many thanks to you.
Regards
Partha

Anonymous said...

This works to separate the pages, but I tried it multiple times and it won't save the header information. A majority of the information is lost during this. I'm trying to separate 307 sheets into individual files so I understand if the volume could cause problems, but I would love some advice on this if you have any!

Satish ankam said...

We are unable to run filename that line was error pls help me..how to creat one Excel sheet to multiple excel files.

TreverHarsey said...

If you find a better price anywhere, Refine Packaging will work to beat your quote. They believe in transparent pricing and work hard to maintain their reputation as the most cost-effective custom boxes provider.

New Launch said...
This comment has been removed by the author.
New Launch said...

It’s tough to come across knowledgeable individuals on this matter, but you sound like you know what you are speaking about! Thanks yearly planner excel