VBA to Open Workbook and VBA to Sum Based on Criteria

Hi

Please can you help.

I have a lot of data that I want to put into another workbook so as to free up space and make the workbook efficient, however, I do not know how to open a workbook in VBA.

Once this workbook is open, I then want to sum the data based on set criteria.

Thanks
Lee

Maybe to open:

Please Login or Register  to view this content.

I'm not clear about "I then want to sum the data based on set criteria"

Thanks for that John.

The procedure I envisage is,

1. User enters criteria codes on an input page of a financial model.
2. User clicks button to calculate summaries (this will then open the workbook where all the data is and a formula (in the financial model workbook) will then sum the data based on the criteria input by the user.

I currently have this formula in the model, but I also have all the data in the model which just makes the workbook too big.

=SUMPRODUCT(SUMIFS(BalSht!$H$2:$H$20175,BalSht!$A$2:$A$20175,CedantCode,BalSht!$L$2:$L$20175,'2. Booked Financials'!$A46))

Thanks
Lee

Can anyone help me?

desperate for help!!!

As my spreadsheet that users are using is so slow

This is a bit confusing. You state that the "model" is too big, but then point out that you are also creating "summaries"

It's not clear if you are trying to make a new "model" by doing these summaries, of if you are simply running
the summaries to generate a report.

One thing to check is whether someone formatted the "model" worksheet. Typically, people click on entire rows or entire columns to do formatting.
This adds tons of size to the file, and slows down processing. You don't want formatting applied to empty cells.

Thanks for your message.

I have a summary page and an input page on the model. On another tab in the same workbook, I have all the raw data 20000k rows+. Cells in the summary page are formulas looking up the raw data based on criteria input by the user on the input page.

See attachment as an example

Opening the file the way JOHN H. DAVIS recommended is how you'd open the Model, if you put all of the data on that worksheet into its own file.

You'd have to figure out how to reference the object for that workbook and worksheet by either using a "With" block or by setting a Workbook variable to it

Typically I open a file using the returned value from the Open method like this:

Please Login or Register  to view this content.

You can the use the Wbk var to establish a Worksheet var like so:

Please Login or Register  to view this content.

You then have a variety of ways to proceed. If you want to break the Model up into several files, you could use code to copy data
from one worksheet to another. Look up the Copy method and the PasteSpecial Method

If you want to summarize data from the Model based on criteria, you can make a lot of use of AutoFilter or
AdvancedFilter methods.

You can also use the Find Method in a loop.

You have many ways to proceed from here.