Macro to Split Data Onto Separate Sheets

Hi, Everyone,

I have a long list of data for numerous companies, and I'd like to separate it out onto different tabs. In the attachment, you'll see data on Tab 1 with (sample) spending for several companies. I would like to split that onto separate sheets--one sheet per advertiser.

Would someone mind providing some help with this? It would be greatly appreciated!


ExcelExample - Need Data Separated.xlsx

Please Login or Register  to view this content.

Thanks, that's amazing! One problem though: When I run the report on the real file (which contains more than 3 companies), it works for the first three companies, then produces nearly 100 blank sheets, and towards the end of the workbook I find the additional company names.

Any ideas?

Again, thank you very much!

Excel is senstive when it comes to creating names. There are many restrictions on naming excel sheets. If excel does not like the name, it just create a blank name. It will not show an error as I have included an error trapping on the code. Having known this issue for some time now, I have inlcuded on the code to delete any names which are not valid names(sheets) from the workbook. So, when ever you run the code, it will delete these empty sheets. If you see any sheet which is not company name, let me know and will have a look again.

Try this

Please Login or Register  to view this content.

Thanks for putting that together. Unfortunately, I"m getting an error after Company 3. I tried debugging and it pointed to this line:

"Sheets.Add(after:=Sheets(Sheets.Count)).Name = e"

Do you still have issues with my code?

Originally Posted by kestefon

"Sheets.Add(after:=Sheets(Sheets.Count)).Name = e"

When you debug, what was the name, variable e ?
You can move the cursor over "e" and name should come up.

Notice:
You can not use the string such as:
1) Exceeds the length of 31 characters.
2) Contains invalid character(s). i.e "?", "\", "/", "[", "]", "*", ":"

Thanks, jindon. In this case the sheet had one of the invalid characters.

Then you need to delete it, no choice.