MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Logging the use of a macro


Posted by Jason Davis on October 05, 2001 4:15 AM

I need to log the use of a macro. Everytime it is run it needs to open an exsiting Excel file (surveillance.xls)find the next empty row and copy a range of data from the file which executed the macro.
(The range of data to be copied contains the date and time stamp and other information)
data range: b1:b9

Any help would be greatly appreciated.


Posted by Jerid on October 05, 2001 5:05 AM

Jason, try this.

Sub YourMacro()

'Add this line
Call LogUse(Range("b1:b9"))

End Sub


Sub LogUse(rRange As Range)

Application.ScreenUpdating = False
Sheet1.Activate
rRange.Copy

Workbooks.Open ("C:\Temp\surveillance.xls")

Application.Range("A1").Select
Do Until ActiveCell.Value = vbNullString
ActiveCell.Offset(1, 0).Select
Loop

ActiveSheet.Paste
Application.CutCopyMode = False

Application.ActiveWorkbook.Close True

Application.Range("A1").Select
Application.ScreenUpdating = True
End Sub

Jerid

Posted by Jason Davis on October 05, 2001 5:41 AM

Works great, thanks a million.

'Add this line Call LogUse(Range("b1:b9"))

Once again you have solved my problem. I just went to Amazon.com and purchased all the books listed as favorites on this web site. With any luck I won't be bothering ya'll now. (or as much anyway.....)

Thanks again. Jason D.

Posted by Jason Davis on October 05, 2001 5:43 AM

Also, thanks for the quick response.

'Add this line Call LogUse(Range("b1:b9")) : End Sub :