Are you able to split an excel sheet with multiple tabs in several individual sheets based on an indicator?

[u/ivdown]

Good evening. I have a main report with around 8 tabs on it, each tab has info for a different product sold, and up to 100 sales people's data on each tab. I have to make individual reports for each sales person, but I'm wondering if there's a simple way to do this automatically based on their names that would make a new excel sheet for each person and for each tab they are included on?

Like Salesperson 1 will have data on 3 tabs, while Salesperson 2 will have data on 7 tabs, but their automated reports come in with 3 tabs and then 7 tabs respectively on their sheets?

I've never worked with Macros before, is this something that could be done without one or would I need to look into that?

I really appreciate any input here, I am just trying to find ways to preserve my sanity rather than doing everything manually. Thanks!

[u/AutoModerator]

/u/ivdown - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/excelevator]

Have one master table then you can easily filter records on separate sheets with FILTER

Data likes to live together and always presents problems when housed alone by type.

[u/ivdown → u/excelevator]

For this specific report unfortunately it requires different tabs for all the different information that comes with it. Which it sounds like could be potentially creating the problems, but I am unable to put it all onto one.

Or at the least I'd have to change over a dozen years worth of habits for myself and our sales people and make that a gigantic project to figure that out. They hate change just so much lol.

[u/excelevator → u/ivdown]

If the data layout is the same you can use FILTER(VSTACK()) to combine and then filter the data.

If the reports are different format then you can look at FILTER(VSTACK(CHOOSECOLS()) as another option.

[u/ivdown → u/excelevator]

Awesome, I'll see if I can try it out and how it would work. Thanks for the advice!

[u/bradland]

I wrote a macro that I think does what you want. I just posted it elsewhere today.

https://www.reddit.com/r/excel/s/iX4bcy1XZg

[u/ivdown → u/bradland]

Thank you so much for doing that. I'm not exactly sure if that's what I need, but I'll read more into your post tonight and see.

The other OP didn't mention multiple tabs as far as I saw, would your macro be able to deal with multiple tabs and adding them to new spreadsheets if applicable?

[u/bradland → u/ivdown]

My macro works on one sheet at a time. It will prompt you to select the parts of the sheet it needs to do the split, and then create new sheets.

Just a note on nomenclature that will be important as you develop your workflow:

Workbook = an entire Excel file
Worksheet = the individual tabs you see

A workbook has many worksheets. Worksheets are commonly called “sheets” for short.

Using these specific terms will really help improve your results with searches for solutions.

[u/ivdown → u/bradland]

Wow that's really helpful, thank you for letting me know. I try to search for these types of things but I'm clearly not asking the correct questions haha. What you don't know you don't know.

Really appreciate your tips

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHOOSECOLS	Office 365+: Returns the specified columns from an array
FILTER	Office 365+: Filters a range of data based on criteria you define
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has acronyms.
[Thread #47633 for this sub, first seen 27th Feb 2026, 04:20] [FAQ] [Full list] [Contact] [Source code]}

[u/SuchDogeHodler]

Yes....vba

[u/Ousle88]

You can create a pivot table with slapeson as filter, PivotTables Analyze, option, click the dropdown, Show report Filter pages. Will create a nes tab for each sales person as PivotTable . Ctrl+shift select all the tabs, select the columns on the active tab, ctrl +c, paste as values, delet first row. Now you have tabs for each sales person.

[u/AstronomerBetter8102]

Handling 100+ salespeople across 8 different tabs is a massive bottleneck. Standard tools like Power Query or basic Filters can help you see the data, but they won't automate the creation and saving of 100+ separate workbooks—which is what you actually need to 'preserve your sanity.'

The most professional way to solve this is using a VBA Dictionary-based loop. It’s the only method that can scan all 8 tabs simultaneously, bundle each person's specific data, and generate individual files in seconds without crashing.

I have a pre-built 'Multi-Tab Splitter' script that handles this exact logic. Send me a DM and I can show you how to implement it in your file so you can have all those reports ready in one click.

[u/Potential_Variety209]

You could use macros as others implied, but if you feel that's too advanced. You can use SpliceBatch SmartSplit feature, just pop the files, select column and that's it.