Like to run macro once on sheet activation

Posted by Ken on November 08, 2001 1:12 PM

I am having a problem trying to get a macro to run on Sheet2 activation, however I need to copy Sheet1 on and this creates an endless repitition to the macro. How do I run once, or do a different way.

Thank you very much
Ken

Posted by Rick on November 08, 2001 1:35 PM

Here is one way to run the macro once for sheet Activate:

Private Sub Worksheet_Activate()
If Range("A2000").Value = 1 Then Exit Sub
'
' add your code here
'
'
' then add the last line
Range("A2000").Value = 1
End Sub

It checks the cell at A2000 which would be empty the first time and at the end of the macro it sets the cell to "1" which it will check the next time the sheet is accessed. If you don't like that address you can pick a different address.

Hope this helps.