Hi,
I have a very simple two sheet Excel file with two macros to act as a
basic helpdesk database. They work fine and fast - but when I share the
workbook, the macros become terribly slow. I don't know why sharing the
workbook slow down so much the macros.

As I don't know VBA, most of the code is "recorded".
I've heard that the "select" command is slowing down execution of the
code.

Perhaps, someone could simply tell me by what to replace the recorded
select code by a smarter alternative.

The NEWLINE macro copies the last existing row, then paste below the
formatting and the validation (drop down lists). Each line in numbered,
so row 30 will be referenced as "30" in column A. Then, the current
time and date is inserted, as well as the "no" marker in the "closed"
column to indicate the call is open.

The SHOWOPEN macro filters on calls that are marked as "not closed",
using autofilter.

The second sheet is a list of names or items for the drop down lists.

Here are the headers for the 'database' sheet.

Ref Date Time Name Incident type Ware Details Description Helpdesk
1 Escalation Helpdesk 2 Solution Closed Date closed Duration Same day


Thanks.
Jim

Here are the macros.

--------------------------------


Sub newline()
'
' newline Macro
' New incident line
'
' Keyboard Shortcut: Ctrl+Shift+N

'Select database sheet
Sheets("database").Select

'Remove auto-filter, if any. (the SHOWOPEN macro will use autofilter to
display open calls)
Range("A1:P1").Select
ActiveSheet.AutoFilterMode = False

' Sort by incident number

Columns("A:P").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

' Select column A
Range("A1").Select

' count number of lines
n = Selection.CurrentRegion.Rows.Count
Rows(n).Copy

' Go to last line and copy it
Rows(n).Select
Selection.Copy

' Go to new line below and paste the validation (time, names...)
Rows(n + 1).Select

Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False

'and paste formats
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

' Send ESC key to deselect row
SendKeys ("{ESCAPE}")


'Increment incident number and type it as the reference number
ActiveCell.Value = n + 1


'Move to the right to Date column
ActiveCell.Offset(0, 1).Select

'insert today's date
ActiveCell.Value = Date

'move to the time cell
ActiveCell.Offset(0, 1).Select

'insert time
ActiveCell.Value = Now


'move to the "closed" column and enter "No" by default (call is
therefore 'open' by default)
ActiveCell.Offset(0, 10).Select
ActiveCell.FormulaR1C1 = "No"

'then return to Name cell
ActiveCell.Offset(0, -9).Select



'
End Sub

Sub SHOWOPEN()
'
' SHOWOPEN Macro
' Macro recorded 24/08/2006 by PE
'Ctrl Shift I

'
'force auto-filter
Range("A1:P1").Select
Selection.AutoFilter

' filter for NO in closed column

Worksheets("database").Range("A1").AutoFilter _
field:=13, _
Criteria1:="no"

'Sort by date (descending) and time, then by status (closed/open)

Columns("A:P").Select
Range("P1").Activate
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending,
Key2:=Range("C2") _
, Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlAscending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

' scroll to top of the screen

Range("A1").Select
End Sub