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:
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.
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..
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.
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
Hello
I am very grateful to you for your code which works fantastic on an Excel file. Many thanks to you.
Regards
Partha
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!
We are unable to run filename that line was error pls help me..how to creat one Excel sheet to multiple excel files.
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.
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
Post a Comment