VBA Formula Bloats to 100MB file

[firefight16]

I have been looking for a formula that would take a Name from a cell and copy that row to the right worksheet. I finally found one that works, but problem is I have a database of 900 names with about 20 divisions so that would be 20 worksheets. The file jumps pretty quick to 50Mb and then stops responding seems the new sheets are the killer. Here is a file Book1.xlsx a sample couple names with actual columns. The other file is what I found that I can use the macro Teachers v2.xlsm

Any ideas to make this macro work?
Teachers v2.xlsm
Book1.xlsx

[mehmetcik]

No I don't see that.

I created a workbook of 20 sheets and it was only 24kb.

So your macro must be creating superflous data.

The attached spreadsheet has a button in cell T1 that will run your macro.

Enjoy.

NB: It may pay to delete the redundant data in the new sheets. eg District No. It isn't a major saving I know.

[firefight16]

When you add sheets it starts to slow down, now add the row title from the other sheet and slower, then add 500 names to first page and slower, by the time I go from U6C, U8C U10C in new sheet names and the data transfers Excel almost comes to a stop hit U14C and excel stops responding then says resources are all used.

[jolivanes]

Select every sheet in Teachers and click on the End key while holding down the Ctrl key. Do the same with the right arrow key.
You'll see that you have some kind of formatting in every cell in your sheets. That is 1,048,576 x 16,384 = a whole bunch of cells.
You can select all the rows below your last entry and delete and do the same for your columns. That should fix that problem.
I have not checked if the code produces that problem but try the above first.
Good luck

[firefight16]

I have already entered my 500 rows in the first sheet, I see what you mean. Maybe I should start again with no data, as I can't highlight thousands of rows to delete Excel pumps out a error.

[jolivanes]

I disabled the code in the This Workbook code window and put the code in it to clean-up.
See if that does it for you.

[jolivanes]

This attachment was, after opening, saved as Teacher.mhtml
After it was saved, I changed the extension from .mhtml to .xls
I opened the file with Excel 2007 (Yes at the warning)
After I saved it again as Teacher.xlsm

[firefight16]

Neither the Teachers v3 or v4 work they don't copy data from the master page to the other worksheets with the teachers names.

I disabled the code in the This Workbook code window and put the code in it to clean-up.
See if that does it for you.

[firefight16]

I installed XSFormatCleaner.xla and ran the cleaner seems to have helped, so far no crashing. Although the code refreshes every time you go to a worksheet, it would work better and faster if it didn't automatically refresh and look for new data from the master sheet. So like a button that I could press so when I enter new data on the master sheet, that all sheets would be updated. Then when I went to the sheet it would not rescan for new data.

[firefight16]

So after the format clean I still get a crash. I am dealing with 500 names right now on the master and 19 extra sheets each a different division. When I go through the tabs, by the 6th or so tab I loose resources, and it asks me to debug. Which I just close and open again the file.

[firefight16]

So no new ideas. Formula works but if I could have it so the worksheets didn't run the macro everytime I change to view a ne sheet. Like a button that once my data is entered I hit the macro and all sheets get their data from page one and it's done till the next time I add new data to sheet one.