I have a spreadsheet that brings in data from another source.

The spreadsheet has a button that you press and it updates the data.The code
is under 'Micrososft Excel Objects' Sheet 1
Private Sub CommandButton2_Click()

Then on another Sheet I wrote a module called "calculations" that sorts
trough the data and does some calculations.

This takes a very long time to update so i want to set it up so that it
autoupdates at midnight everynight. so first it will update the spreadsheet
that brings in the info from another source then it will do the calculations
from my module.

You may want to add the following at the beginning of your code
Application.ScreenUpdating = FALSE
Application.Calculation = XLCalculationManual

and at the end
Application.ScreenUpdating = TRUE
Application.Calculation = XLCalculationAutomatic

It may speed up execution.

--
HTH,
Barb Reinhardt



"computers hate me" wrote:

I have a spreadsheet that brings in data from another source.

The spreadsheet has a button that you press and it updates the data.The code
is under 'Micrososft Excel Objects' Sheet 1
Private Sub CommandButton2_Click()

Then on another Sheet I wrote a module called "calculations" that sorts
trough the data and does some calculations.

This takes a very long time to update so i want to set it up so that it
autoupdates at midnight everynight. so first it will update the spreadsheet
that brings in the info from another source then it will do the calculations
from my module.

We already tried that.

The module that i wrote calculates really quick. What takes forever is
bringing in the data from the other source. When i say forever i mean like 2
hours to update. this is because its ALOT of data it fills up the entire
worksheet.

Thats why the only thing that wethink we can do is to set it up to
autoupdate at night.

"Barb Reinhardt" wrote:

You may want to add the following at the beginning of your code
Application.ScreenUpdating = FALSE
Application.Calculation = XLCalculationManual

and at the end
Application.ScreenUpdating = TRUE
Application.Calculation = XLCalculationAutomatic

It may speed up execution.

--
HTH,
Barb Reinhardt



"computers hate me" wrote:

I have a spreadsheet that brings in data from another source.

The spreadsheet has a button that you press and it updates the data.The code
is under 'Micrososft Excel Objects' Sheet 1
Private Sub CommandButton2_Click()

Then on another Sheet I wrote a module called "calculations" that sorts
trough the data and does some calculations.

This takes a very long time to update so i want to set it up so that it
autoupdates at midnight everynight. so first it will update the spreadsheet
that brings in the info from another source then it will do the calculations
from my module.

OK, you're going to have to create an Workbook_Open macro that starts
everything up.

You'll also need to go to your task manager (CTRL ALT DEL) and under the
applications tab, add a new task. You can have it open your file at midnight
and the Workbook_Open macro will do it's stuff.

I've tried doing something similar, by my system sometimes reboots overnight
when it gets patches and things don't run as I expect.

Make sense?
--
HTH,
Barb Reinhardt



"computers hate me" wrote:

We already tried that.

The module that i wrote calculates really quick. What takes forever is
bringing in the data from the other source. When i say forever i mean like 2
hours to update. this is because its ALOT of data it fills up the entire
worksheet.

Thats why the only thing that wethink we can do is to set it up to
autoupdate at night.

"Barb Reinhardt" wrote:

You may want to add the following at the beginning of your code
Application.ScreenUpdating = FALSE
Application.Calculation = XLCalculationManual

and at the end
Application.ScreenUpdating = TRUE
Application.Calculation = XLCalculationAutomatic

It may speed up execution.

--
HTH,
Barb Reinhardt



"computers hate me" wrote:

I have a spreadsheet that brings in data from another source.

The spreadsheet has a button that you press and it updates the data.The code
is under 'Micrososft Excel Objects' Sheet 1
Private Sub CommandButton2_Click()

Then on another Sheet I wrote a module called "calculations" that sorts
trough the data and does some calculations.

This takes a very long time to update so i want to set it up so that it
autoupdates at midnight everynight. so first it will update the spreadsheet
that brings in the info from another source then it will do the calculations
from my module.