faster running code

Alan, not had a detailed look but I would say it may be worth considering forcing calc to manual at commencement of routine (storing present calc method before altering & resetting to original at end)

'DECLARE
Dim xlCalc As XlCalculation
'ASSIGN
xlCalc = Application.Calculation
'APPLY
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With
'....your code
'RESET
With Application
    .ScreenUpdating = True
    .Calculation = xlCalc
    .EnableEvents = True
End With

Technically as everything is working at Application level you can do

Sub 
Dim xlCalc etc...
With Application
    'entire code
End With
End Sub

I didn't see an Error Handler set up -- given you're disabling events & screen etc I would advise you create one -- apologies if I missed something.

There's a lot of code to go through. One point, this will not speed it up, your variables & constants should be declared at the top of the code. It's easier to read like that.

You use Loops to find departments. Advanced Filter -> Unique values might be faster. Just copy the results to an out of the way column.

Your Find, FindNext code might be faster if replaced by AutoFilter

I'm not sure if it will have much impact on speed but where you are referring to objects for more than one action they can be grouped in a secondary With statement eg:

'eg
   LastItemColD = Module1.LastCell(ws).Column
    LastItemRowD = Module1.LastCell(ws).Row    
    LastItemColAA = Module1.LastCell(ws2).Column
    LastItemRowAA = Module1.LastCell(ws2).Row
'can become
with module1
with .lastcell(ws)
   LastItemColD = .Column
    LastItemRowD = .Row    
end with
with .lastcell(ws2)
    LastItemColAA = .Column
    LastItemRowAA = .Row
end with
end with
'and this...
    'clear the current data; format the date row:
    With Me
    .range(Cells(intSummaryDateRow, 1), Cells(36, Cells.Columns.Count)).Clear
    .Rows(intSummaryDateRow).NumberFormat = "mmm yy"
    .Rows(intSummaryDateRow).Font.Bold = True
'...
'could become this...
   'clear the current data; format the date row:
    With Me
    .range(Cells(intSummaryDateRow, 1), Cells(36, Cells.Columns.Count)).Clear
    with .Rows(intSummaryDateRow)
.NumberFormat = "mmm yy"
.Font.Bold = True
end with
'...

hth
Rob

Originally Posted by shortman_alan

thankyou DonkeyOte, this actual made a bit of a difference! although it has made some of my other sub's not work quit as well.
will it be needed to declare calculation as ether automatic or semiautomatic at the start of the these programs?
cheers, alan

also i have been using excel 2003 and many of the machines that this document will be used on will run 2007. i was just wondering, functions such as trim() and format() are they still the same in 2007 as i have encountered a "cannot find in library" error and these are pointed to be the debugger.

cheers

Re: calculation -- if you are invoking other routines from within the same process, no - calculation is set to application level thus applies to everything -- so until it is reset to the initial calc method it will run on manual mode... you may well at certain points in your code need to force a calculation so as to ensure your variables reflect current position as the code progresses but this is still preferable to having the entire app recalculate as you alter data and/or conduct a "volatile" action (moreso if you have volatile functions in your model) ... so step through your code and determine as/when you need to enforce a calculation.

Re: 2003 to 2007 -- I'm not aware of anything that works in 2003 but not 2007 other than perhaps FileSearch (there may be others) ... there are as always a few backwards compatible issues ... on the machine that generates the error go to References in VBE and see if anything is listed as MISSING... if so post back.

Just to reiterate I didn't read through all of your code so apologies if I've missed something vital.