asked on

Excel VBA

I need to separate the data for each school (Column B) listed in the attached workbook into separate sheets. I will need to run the code to refresh the sheets when the data source is changed and have put together some vba code. This will work as long as the school name is less than 31 characters. I then included a LEFT function to extract only the first 31 characters - this works the first time I run the code, but not in reruns. Also, the workbook is running really slowly. Could anyone advise what I can do to to fix this?
Offers-And-Acceptances.xlsm

John Tsioumpris

Have you ever considered moving to MsAccess...i downloaded your Excel but somehow it wouldn't open ...
Anyway i have prepared a small demo in Access
The Combobox "Filter Schools" allow you to filter to the ones you want
double clicking the combo removes the filtering
S.accdb

Suzanne McMorrow

ASKER

Thanks John, but unfortunately this has be in Excel.

Martin Liss

Is your purpose to be able to split the schools into separate sheets where each school sheet has the same columns as the All School Summary sheet?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fabrice Lambert

Well, I wasn't able to work much with the workbook, even commenting out all VBA didn't help.

I also noticed that it take a lot off mircroprossessor ressources (more than 15%), guess there is something wrong with your formulas.
The only suggestion I have about this is to remove them all, and add them back one by one till you find the culpitr (whenever the workbook slow down again).

Suzanne McMorrow

ASKER

Thank you Martin Liss. This is exactly what I was looking for and works perfectly.

Suzanne McMorrow

ASKER

Martin, sorry to be a pain but I need to convert the range to a table and am not sure how to reference this in your SplitSchools macro. Can you advise?

Martin Liss

Let me see what I can do. I assume the table should include all the columns.

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Suzanne McMorrow

ASKER

Thanks Martin, this is exactly what I needed.

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015, 2017
Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
Experts Exchange Top Expert VBA 2018
Experts Exchange Distinguished Expert in Excel 2018