automatically run script

[dangerd]

Hi there,

I have the following code which I want to execute either:

- At regular time intervals (30 sec)
- When I activate the worksheet
- When smt changes in sheet 1 column A.

Thanks

Sub MirrorColumnA()
Const sourceSheetName = "Sheet1" ' change as needed
Dim sourceSheet As Worksheet
Const destinationSheetName = "Sheet2" ' change as needed
Dim destSheet As Worksheet
Dim anyRangeAddress As String
Dim sourceRange As Range
Dim destRange As Range


Set sourceSheet = Worksheets(sourceSheetName)
Set destSheet = Worksheets(destinationSheetName)
anyRangeAddress = "A1:A" & _
sourceSheet.Range("A" & Rows.Count).End(xlUp).Row
Set sourceRange = sourceSheet.Range(anyRangeAddress)
Set destRange = destSheet.Range(anyRangeAddress)
destRange.Value = sourceRange.Value
Set destRange = Nothing
Set destSheet = Nothing
Set sourceRange = Nothing
Set sourceSheet = Nothing
End Sub

 

[Dave Peterson]

#1. You may want to take a look at Chip Pearson's notes on .ontime:
http://www.cpearson.com/excel/OnTime.aspx

#2 & 3. Look at a worksheet event (this code goes behind the worksheet):

Option Explicit
Private Sub Worksheet_Activate()
call MirrorColumnA
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A:A")) Is Nothing Then
Exit Sub
End If
Call mirrorColumnA
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

 

[dangerd]

#1.  You may want to take a look at Chip Pearson's notes on .ontime:http://www.cpearson.com/excel/OnTime.aspx

#2 & 3.  Look at a worksheet event (this code goes behind the worksheet):

Option Explicit
Private Sub Worksheet_Activate()
   call MirrorColumnA
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A:A")) Is Nothing Then
        Exit Sub
    End If    
    Call mirrorColumnA
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson has some instructions on events:http://www.cpearson.com/excel/Events.aspx

David McRitchie has some notes, too:http://www.mvps.org/dmcritchie/excel/event.htm









--

Dave Peterson- Hide quoted text -

- Show quoted text -

Whow...Thanks!