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

#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 wrote:

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

On Jan 25, 2:46*pm, Dave Peterson wrote:
#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 wrote:

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- Hide quoted text -

- Show quoted text -

Whow...Thanks!