any tweeks to improve these macros

[khalid79m]

file a.xls Module 1

Private Sub USERCLOSE()

Sheets("UserData").Select
Range("A10").Select
ActiveCell.Value = Application.UserName

listofusers = Array("ian", "andy", "phil", "muneer", "mel", "rachelh", "rachelo", "liz", "jemma", "helen", "christine", "sharon", "julie", "danielle", "linda", "karen", "emma")

For n = 0 To UBound(listofusers)
'MsgBox (listofusers(n)) & "." & n & " " & LCase(ActiveCell.Value)(testing purposes)
If LCase(ActiveCell.Value) = (listofusers(n)) Then
Run "UserdataOnClose"
Exit Sub
End If
Next n


End Sub
Private Sub USEROPEN()

Sheets("UserData").Select
Range("A10").Select
ActiveCell.Value = Application.UserName

listofusers = Array("ian", "andy", "phil", "muneer", "mel", "rachelh", "rachelo", "liz", "jemma", "helen", "christine", "sharon", "julie", "danielle", "linda", "karen", "emma")

For n = 0 To UBound(listofusers)
'MsgBox (listofusers(n)) & "." & n & " " & LCase(ActiveCell.Value)(testing purposes)
If LCase(ActiveCell.Value) = (listofusers(n)) Then
Run "UserdataOnOpen"
Exit Sub
End If
Next n


End Sub
Private Sub UserDataOnOpen()

MsgBox (" Hello " & LCase(ActiveCell.Value))

Sheets("UserData").Select
Range("A1").Select

ActiveCell.Formula = "Open"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = Now()
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=COUNTIF(CallData!C[1],""0"")"
ActiveCell.Offset(0, 4).Select

ActiveCell.Value = ThisWorkbook.Name


Range("A1:H1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("CallData").Select
ActiveWindow.Zoom = 100
ActiveSheet.Range("A1").Select





End Sub

Private Sub UserDataOnClose()

MsgBox ("Bye " & LCase(ActiveCell.Value))

Sheets("UserData").Select
Range("A2").Select

ActiveCell.Formula = "Closed"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = Now()
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=COUNTIF(CallData!C[1],""0"")"
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=RC[-3]-R[-1]C[-3]"
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=R[-1]C[-2]-RC[-2]"
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ThisWorkbook.Name

Range("A2:H2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Sheets("CallData").Select
ActiveWindow.Zoom = 100
ActiveSheet.Range("A1").Select

Run "CopyPaste"

End Sub
Private Sub CopyPaste()
Dim ws As Worksheet
Dim rng As Range

Set ws = Sheets("UserData")
ws.Select
Set rng = ws.Range("A1:H2")

Workbooks.Open Filename:="C:\Documents and Settings\Owner\Desktop\usage.xls"


Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
rng.Copy Destination:=ActiveCell

End Sub

file a.xls Thisworkbook
[QUOTE]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Run "USERCLOSE"

End Sub
Private Sub Workbook_Open()

Run "USEROPEN"

End Sub
[\QUOTE]

[mudraker]

Always Declare all variables
User option is to use Option Explicit as the 1st entry on every Module page - This forces you to declare all Variables.
You can turn this option on for any new Module sheet that gets added by going to the Tools Munu > Options and ticking the Require Variable Declaration box

Do not select cells or ranges unless absolutly necessary

Some examples of replacing select

In Private Sub USERCLOSE & Private Sub USEROPEN()
Change

Range("A10").Select
ActiveCell.Value = Application.UserName

to

Range("A10").Value = Application.UserName


In Private Sub UserDataOnOpen()
Change
Range("A1").Select

ActiveCell.Formula = "Open"
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = Now()
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Application.UserName
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=COUNTIF(CallData!C[1],""0"")"
ActiveCell.Offset(0, 4).Select

ActiveCell.Value = ThisWorkbook.Name

to
Range("b1:e1").Value = Array("Open", Now(), _
Application.UserName, "=COUNTIF(CallData!C[1],""0"")")
Range("h1").Value = ThisWorkbook.Name

In Private Sub UserDataOnClose() make similar changes