Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
slow macros when sharing workbook
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros Causing Workbook to Run Very Slow | Excel Discussion (Misc queries) | |||
Sharing a Workbook That Includes Macros | Excel Discussion (Misc queries) | |||
Sharing workbook with macros | Excel Discussion (Misc queries) | |||
Macros/Tracking/WorkBook Sharing Help Please | Excel Programming | |||
Macros/Tracking/WorkBook Sharing Help Please | Excel Programming |