Cách khôi phục sheet đã xóa trong excel đang là chủ đề được rất nhiều người tìm kiếm. Vậy nên hôm nay Học Excel sẽ đưa đến các bạn chủ đề Cách khôi phục sheet đã xóa trong excel | Split a Master Spreadsheet into Multiple Sheets with 1 click – VBA for Beginner thông qua video và nội dung dưới đây:
Mua sản phẩm này trên Shopee: Mua Ngay
Mua sản phẩm này trên Lazada: Mua Ngay
Mua sản phẩm này trên Tiki: Mua Ngay
Want to learn how to design a salary structure? Check:
FREE template for my video: Excel for HR – Create Annual Employee Salary Increase Template from Scratch. You can download and try it out yourself here:
FREE actual workbook for my video “Split a Master Spreadsheet into Multiple Sheets with 1 click – VBA for Beginner”. You can download and try it out yourself here:
New course Launched! I created it to show you step-by-step how to design a salary structure with regression analysis in Excel. Check out the detail here:
Topic: Split a master tab into multiple sub tabs with 1 click
Scenario: You want to split the data on a master file into multiple small sub-tabs by a chosen criteria (eg. Department, Country, etc.)
Function: Macro for Copy sheet, AutoFilter, and Loop
Workbook download:
You can download and try it out yourself here –
I do have a course to provide additional info around this VBA function in case you are interested in learning more:
Related Video:
Excel Macro/VBA – Splitting a Master File
Troubleshooting when your code does not work:
Troubleshooting: Why my Split Macro does not work?
Save Sheets as Multiple Files with 1 click
Save Multiple Sheets as Separate PDF Files
Send or Draft Multiple Outlook Emails with Attachment
***Macro Code SEE COMMENT FOR IMPORTANT NOTICE***
Sub SplitandFilterSheet()
‘Step 1 – Name your ranges and Copy sheet
‘Step 2 – Filter by Department and delete rows not applicable
‘Step 3 – Loop until the end of the list
Dim Splitcode As Range
Sheets(“Master”).Select
Set Splitcode = Range(“Splitcode”)
For Each cell In Splitcode
Sheets(“Master”).Copy After:=Worksheets(Sheets.Count)
ActiveSheet.Name = cell.Value
With ActiveWorkbook.Sheets(cell.Value).Range(“MasterData”)
.AutoFilter Field:=6, Criteria1:=”NOT EQUAL TO” & cell.Value, Operator:=xlFilterValues
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilter.ShowAllData
Next cell
End Sub
******Follow-up Consulting Services******
If you have specific question regarding your issue, you can email me at the email here Note that there will be a fee of US$200 charged for solving your issue. The turnaround is within 24 hours. Any follow-up issue in 3 days will also be answered with no charge. Payment link:
******More Videos in Playlists******
Power BI for Beginners:
Power BI for Advanced Users:
Excel for HR
Excel for HR – Master Class
Excel Macro – Beginner
Excel Macro/VBA – Splitting a Master File
Excel Macro/VBA – Auto-hide Rows or Columns
Excel Charts Data Visualization
Excel Vlookup Function
Excel Pivot Table Function
Excel Array Function
Excel Index and Match Function
Excel Solver/Goal Seek Functions
Excel Cell Formatting Solutions
HR Analytics – Merit Matrix
HR Analytics – Salary Structure
Excel Tricks
Excel Troubleshooting
Fun HR Topics
For more successful stories, view at:
#ExcelforHR#HRAnalytics#Excel#HR
Tag: Cách khôi phục sheet đã xóa trong excel, Split master tab menjadi beberapa sub tab, Split a Master Spreadsheet into Multiple Sheets, Split a master tab into multiple sub tabs, split master sheet, split spreadsheet in excel with macro, how to use macro to divide sheet, split data into multiple worksheets, split excel file into multiple, split excel sheet into multiple sheets based on column,एक मास्टर टैब को कई उप टैब में विभाजित करें, how to use macro to break sheet, how to split one excel sheet into multiple files
Cảm ơn các bạn đã theo dõi nội dung Cách khôi phục sheet đã xóa trong excel | Split a Master Spreadsheet into Multiple Sheets with 1 click – VBA for Beginner. Học Excel hy vọng đã giúp được bạn giải đáp được vấn đề, mọi thắc mắc hay comment xuống phía dưới.
Xem thêm: https://taichinh4u.net/category/chia-se
This is working for me – but it does not have each sheet separated by filter view as it is supposed to. Do you know what the problem/solution may be? Thanks!!
getting runtime error 424 at auto filter level where asked to filter based on colum 2 which is division
thanks , This is really wonderful appreciate your help on this…
Great work
I am getting error wirth .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Please assist
Hi, it looks great! I used code, but it stopped at the Offset line, didn't delete. what could be the problem?
That is a really cool thing thank you so much, I want to take some VBA courses how can I reach you out ?
I'm getting invalid outside procedure error. I cannot for the life of me, understand what it means. Help! 😂
thank you …you explanation very easy to understand
I have 3 questions
1) how do I make it to work in Google sheets
2) is it live formula, if a new department is added will it automatically create another tab or if we add new data in a row will it be automatically posted to that department tab
3) if we want to leave out a column say c from your master data how do we do that.
Is there a way to have this code not overwrite the previous data and add onto the next available line? In otherwords. Be able to refresh and update several times a day if someone else modifies the data ?
run time error 1004
Worked great for me and saved a lot of time. Tip: Max length for SplitCode range is 31. This is a limitation in the Excel Sheet name with a max length of 31 chars. If you change the SplitCode range's value, the data in MasterData also need to change as they will not match.
Help! I'm getting told to debug line .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete Any idea why?
Thank you for sharing this! Is there a way to segregate the master sheet into existing spreadsheet instead of creating a new spreadsheet?
Thank you very much for this step by step tutorial. It finally works for me !
Thanks a lot for the tutorial. But is there anyway to split the master data without VBA? I have a file that needed to be shared with multiple users at MSteam. Unfortunately, VBA is not supported in MSTeam.
If anyones code is working and sheets are being created but not filtering the data.. change NOT EQUAL TO to <>
Thank you for sharing. if the split data is being / needs to be updated by multiple user how will you sync the update to the master spreadsheet?
my spreadsheet is showing debug for overlapping for below code , can you pls help
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Everything works but the filtered items don’t delete in the new sheets, any fix?
For some reason, the departments split into different tabs, but none of the data was In the split tabs! Help? What did I do wrong?
Thank you very much . You save my day 👍👍
If I have each department updating information in their specific sub tabs , does this information update the master sheet with the new data and vise versa.
My splitcode data is number which in text format. I copy your code and it only split into different tab without any data (only the header). I’m not sure where goes wrong. Are you able to advice?
Very informative video I need this. Thanks for sharing.
For those of you getting the Run Time Code 1004: No Cells Were Found, try removing the following bits of code from your macro: .SpecialCells(xlCellTypeVisible)
Hi Mam, I have very less knowledge in Macros
I need your assistance for one of data.
I have one ‘master list’ consists of Project name, Contractor name, Ref No, Project No. Value of the Project
Here are my requirements
1. I have to create sheets based on the project no from the ‘Master List’ from ‘Template’ sheet
2. ‘Template’ sheet contains payment information for each project and linked information of Project name, Contractor name, Ref No, Project No. Value of the Project from ‘Master List’
3. Whenever I enter new project information in the ‘Master List’ a new sheet to be created in the name of Project number containing the information of Project name, contractor etc.,
Hope you understand my requirements. And sorry for my English
Hi, what if I have a master list from a google sheet which is updated daily. Can i use the same method to sort the data?
This is amazing and saves so much time. Is there a way of splitting by department in individual tabs & then the country (using your data) for each department as a secondary action?
This split into multiple tabs with the correct info, however it didn't delete out the information that I did not want (aka it has all categories listed on each tab, but the naming of the tab worked)
can you tell me how to do this is google spreadsheets also Instead of seperating the data, i want to create tabs on the number of spreadsheets that I have in a particular coloum
Thank you for this great solution. One more thing for anyone who encounter error '1004' you can fix this issue by getting red of space by using TRIM() function in Excel
Please assist, why does my Macro get stuck at the .Autofilter section with Runtime error424 object required?
First Macro & Run Successfully, thanks alot Love you 😊😊
Hi, great macro, thanks for sharing. I do have another macro I could not figure out, do uou mind sharing the code to split a sheet into different tabs per row of max. 130 row? So for every 130 rows I need a new sheet. And eventually save each sheet to a new file. Thank you!
what about if i need multi criteria (eg. Department & country) ?
HI, I continue to get to error 1004. Can you please assist with this?
Thank you very much!. Your explanation was throughout. I did not know anything about macros, and I was able to follow. <3
Thanks a lot. It awesome
You can download the workbook and try it out yourself here -> https://bit.ly/2UmeX2v
I also have a course to provide additional info around this VBA function in case you are interested in learning more: https://caripros-hr-analytics.teachable.com/p/ultimate-end-to-end-course-split-a-master-data-file-into-multiple-spreadsheets-with-excel-vba
Every time I run the code Error 9: subscript out of range appear. How to solve this? please help
Awesome tip .Thanks a lot.
Just one variation I need the data to be saved in separate excel files in the same folder instead of separate sheets. Is that possible?
Hello Ma'am, this code works really well for me. But I wanted to ask if I can split the data further restricting to 10 rows per sheet instead of the entire Department data in one sheet. Your help would be appreciated. For e.g. if Dept 1 has 30 rows, i need to split it into 3 sheets of 10 rows each not entirely in one sheet
you have used this command sheets("master").copy after:=worksheets(sheets.count) but I do not want to copy blank cells into new sheet ( I know you are deleting rows using criteria in 2nd sheet but still if I have 50000 row mention in row A and I have in puted data only up till 300 in row b to row m so that macro work little but faster
Hello mam, thanks for the lessons on VBA coding it has helped and saved lot of time and improved quality of output. Is it possible to copy that specific column to new workbook and save in field name.
For example in the given video excel workbook in the name of department.
Please let me the solution sir.
Thanks in advance.
Hello,
I haven't worked with Macros VBA for a very long time now. I'm almost there. However, the splitting is failing to filter out (or delete) the data that not correspond to a tab. It's creating all the tabs, naming them correctly, but coping and pasting the whole information from the main tab. Any help please?
We need to split data and with sum and draft mail
End of the we want total please
while processing the code it is working fine for the first value in the criteria range however from 2nd value onwards it shows the error "delete method of range class failed" . how to fix
How can you create a master sheet with different values and variables from different sheets?
I have many headers but I need to mmake a mtd summary master sheet?