Multiple Worksheets Referencing One Worksheet

[mathewsn0]

I have a worksheet with about 60,000 rows and 20 columns. I need to make about 20 copies of the main worksheet and then filter slightly differently on each of the other worksheets. This is a daily process so I'd like to automate it more. I started off pretty basic with just a formula referencing the main worksheet but my file size got very large very quick. Is there a better way to do this that I'm overlooking? It seems like there should be but I'm stumped.

 

[Joe4]

Welcome to the Board!

What you are describing is a database model. As such, a database program, such as Microsoft Access, would handle this kind of stuff better. You can set up interactive queries to filter the data the way you like at run-time (or even store the queries separately within duplicating any of the data).

If you are unable to use Access, perhaps look at using Filters or Advanced Filters on your original dataset, so there is no need for additional sheets, just change the filters on the data to see what you want.

 

[mathewsn0]

Welcome to the Board!

What you are describing is a database model. As such, a database program, such as Microsoft Access, would handle this kind of stuff better. You can set up interactive queries to filter the data the way you like at run-time (or even store the queries separately within duplicating any of the data).

If you are unable to use Access, perhaps look at using Filters or Advanced Filters on your original dataset, so there is no need for additional sheets, just change the filters on the data to see what you want.

Thank you for the quick reply. I thought about Access and may end up needing to go that way but would prefer to stay in Excel. Unfortunately the multiple worksheets is a requirement. I'll look into the advanced filters and see if that'll do the trick.

 

[Joe4]

OK. Repeating that much data in your workbook 20 times will probably bog things down, especially if you have any formulas doing calculations.

One of the advantages of Access is that you only need one copy of the data. Having multiple queries does not increase the database size significantly, as you still only have one copy of the data (just different "views" into it).

 

[mathewsn0]

Repeating that much data in your workbook 20 times will probably bog things down, especially if you have any formulas doing calculations.

Do you suppose recording a macro would work? I create a copy of the main worksheet, filter, repeat about 20 times.

 

[Joe4]

Do you suppose recording a macro would work?

That could be a start. You could then add a loop to your VBA code to repeat that process 20 times (at least the copy part).
Assuming all your filters are different, you probably wouldn't be able to incldue that part in the loop, but you can use the Macro Recorder to get the code to apply the filter for each sheet that you want.