Large Workbook - trouble opening

[rbecker]

I have a couple of workbooks (Excel '07) that I'm having trouble with them
being slow to open. One, I sort of understand why it's slow as it has a lot
of calculations, the other I'm not sure why. Here's the senarios an maybe
someone could suggest a way to get them to open quicker......

1. The most recent problem is with a worksheet where I'm taking a list of
our customers and the reoccuring work we do for them (and other important
info) and trying to make rolodex cards for each customer. I have the
complete list on sheet 1. Then on each subsequent sheet I have only one
customer's info per sheet in a format that will fit on the rolodex cards that
I want to use. I get the customer's info by connecting back to the sheet 1
(i.e. "=Sheet1!A1" in the cell) sometimes I have an IF statement if there is
a possiblity that the cell on Sheet 1 could be blank but other than that no
"calculations" per se. Is there any way to keep this one from being a pain
in the you know what everytime I open it or am I just SOL? I'm only on the
letter C.

2. The second worksheet is the one that is a doosey. I used to do this one
in Excel '03 before my computer crashed and I upgraded to '07. I never had
problems opening it until I got '07. I've tried saving it as every option
available but it doesn't help speed things up. I have to do a report for my
boss showing our billable time vs non-billable time and include what each
technician is paid vs what is worked (among other things). There are TONS of
calcs, not just on each worksheet for each week of the year (yes, I said
year) but back to the summary sheet which is what is printed out. Is there
any way I can have it do the calculations ONLY when the workbook is saved,
not when opened? I think that could possibly make it open a lot faster. I
try to not let the data entry part get so bad that I have to enter weeks at a
time so I could wait to save until then end. Then if it took a while to
save, I could just file the time folder away while it's saving.

Sorry this is so long but it's not easy to describe in a short way.

 

[Don Guillett]

1. I have a mailing list set up where I double click on the name and the
envelope tab is filled out. So, why not have ONE form that is populated
thusly from the ONE database.

2. Set calculation to manual and in the ThisWorkbook module set up a
before_close or before_save event to do the calculation. You might also try
to make your project more efficient. Some use many formulas where one will
do.

 

[fdibbins]

on your 2nd problem, file taking along time to open...maybe setting it to
manual calc will save time on opening? when you save it at the end of the
session, it will automatically recalc the entire s/sheet before it saves?
(may help with the 1st problem too?)

/tools/options/calculations tab - select manual calc
Also, maybe you could consider converting the calcs from old data, to
values? that way it wont keep going through and calc'ing answers to things
that have already been calc'd and that wont change ever again?

 

[rbecker]

Thanks for your quick response, Don!!

1. How did you do the mailing list? I might actually be able to use that.

2. I will have to look to see if I can scale down on the calculations. I
would like to try your suggestion "Set calculation to manual and in the
ThisWorkbook module set up a before_close or before_save event to do the
calculation." but where do I find it? I'm still learning where everything
is on '07.

 

[Don Guillett]

Right click sheet tab>view code>insert this>modify to suit
While in the vbe (visual basic editor) look for the ThisWorkbook module

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column = 1 Then
If ActiveCell.Offset(0, 2) <> "" Then
Title = ActiveCell.Offset(0, 2)
Else
Title = ""
End If
If ActiveCell.Offset(0, 1) <> "" Then
FirstName = ActiveCell.Offset(0, 1) & " "
Else
FirstName = ""
End If
LastName = ActiveCell
ADDRESSEE = Application.Proper(Title + FirstName + LastName)
[envelope!c6] = ADDRESSEE
[envelope!c7] = ActiveCell.Offset(0, 3)
[envelope!c8] = ActiveCell.Offset(0, 4)
[envelope!c9] = ActiveCell.Offset(0, 5)
Sheets("envelope").Select
End If
End Sub

 

[rbecker]

Never thought about converting to values on the 2nd one. You're right, once
the numbers get figured, the chances of them changing are slim (since they
refer to payroll/completed jobs). That would cut down on a LOT of the calcs.
THANKS!!