Run macro just once

Posted by Ken on October 31, 2001 6:32 AM

Hi I have this macro, but I don't know how to make it run just once when I activate Sheet2. Thanks

Private Sub Worksheet_Activate()
Sheets("Sheet1").Select
Cells.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
End Sub

Posted by Jonathan on October 31, 2001 7:49 AM

The first time you run it have it write an 'X', say, into some far off cell you'll never use, eg. IV4. Include a line to check for an 'X' in that cell, and if it's there, Exit Sub.

Like:

If Range("IV4").Value = "X" Then
Exit Sub
Else
Range("IV4").Value = "X"
End If

Posted by Barrie Davidson on October 31, 2001 7:54 AM

Further to Jonathon's answer

Great solution Jonathon! But, when you close the workbook you'll need to clear this indicator cell (otherwise the next time you open the workbook the macro won't fire the first time you activate the worksheet). Put something like this in your workbook close event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("YourSheetName").Range("IV4").ClearContents
End Sub

Regards,
BarrieBarrie Davidson