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 

3 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.

Popular Posts