MrExcel.com - Your One Stop for Excel Tips & Solutions

Like to run macro once, whoops forgot to send macro

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

I am posting my message again I forgot to post the macro. I need this macro to not run endlessly between Sheet1 and Sheet2 on Sheet2's activation. Thank you very much.

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

Ken

Posted by Bertie Bagshot on November 08, 2001 1:46 PM


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


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

Here is one way to do it:

Private Sub Worksheet_Activate()
If Range("A2000").Value = 1 Then Exit Sub
Sheets("Sheet1").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet2").Range("A1").Select
Application.CutCopyMode = False
Range("A2000").Value = 1
End Sub

You can pick any cell for the check cell (I used A2000) but this macro would be better off in a module with a name like InitCopy() and then you could run it any time you wanted to.