I am trying to create a simple macro

[davey11372]

For a given cell range (eg. "A1:Z3") on Sheet1 - if any cell within that range - populates any one of the following values - "Txt1" or "Txt2" or "Txt3" - then I want the corresponding cell (same cell address) on sheet2 - to be populated with the same value ("Txt1" or "Txt2" or "Txt3") respectively.

I appreciate any assistance with the same.

Also, to have this macro up and running (continuously) as soon as the workbook is opened, do I have to do anything --- or does it start and run automatically ??

Thank you.

 

[ravishankar]

Hi
Give these codes a try

Sub auto_open()
Dim a As Long, b As Long
Dim C As String
    For a = 1 To 26
        For b = 1 To 3
        C = Sheets("sheet1").Cells(b, a)
            If C = "Txt1" Or C = "Txt2" Or C = "Txt3" Then
            Sheets("sheet2").Cells(b, a) = Sheets("sheet1").Cells(b, a)
            End If
        Next b
    Next a
MsgBox "updated"
End Sub

ravi

 

[davey11372]

Thanks Ravi, for the code.

It does what you have coded it to do. However, once the sheet is open, if I make changes to cell values on sheet1, it does not automatically/dynamically populate the corresponding changes in Sheet2. Only when I close the workbook, and re-open it, only then your code updates the values with a msgbox. Is it possible to script is so that it can update the corresponding values on sheet2 as the cell values in sheet1 are being updated dynamically??

 

[Ed in Aus]

Could this be done with a formula on sheet 2?

in Sheet 2 A1
=IF((OR(Sheet1!A1="Txt1",Sheet1!A1="Txt2",Sheet1!A1="Txt3"))=TRUE,Sheet1!A1,"")

 

[Domenic]

Try the following code, which needs to be copied/pasted in the module for Sheet1...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:Z3")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Txt1", "Txt2", "Txt3"
            Target.Copy Worksheets("Sheet2").Range(Target.Address)
    End Select
End Sub

 

[davey11372]

Sorry guys, I only want a macro for this function. Or may be a "function" instead of a "macro". The reason being, I have a worksheet_change event in the sheet already. But, since the worksheet_change event does not work with formulas (or rather needs a user/manual change to the worksheet), from online research, I seems a 'macro' could sidestep this and make the worksheet_change recognize the 'macro' functionality as a user change ... I hope I am clear in my explanation.

I am trying to overcome the fact that cell values derived from formulas in my worksheet are not being recognized as worksheet_change events.

Thank you.

 

[Domenic]

A couple of possibilities...

1. Tie the Worksheet_Change event to whatever happens to update the formulas.
   
2. Use the Worksheet_Activate event instead.

How do you want to proceed?

 

[davey11372]

I can't say which to go with, because I am not knowledgeable of the difference. If you could explain the difference a bit. But, any one will do, as long as I can bypass the problem of formulas not being recognized as event changes.

Thank you.

 

[Domenic]

Actually, I do have one other question...

Will the values returned by the formulas in A1:Z3 change over time. For example, if a formula returns "Txt2", can this same formula return a value other than "Txt1", "Txt2", or "Txt3" at a later time. If so, do the corresponding values in Sheet2 need to be cleared?​

 

[davey11372]

If I understand your question, I would say Yes and Yes. The values may be other than Txt1, txt2, txt3 at a later time and the values in Sheet 2 could be cleared.


(trying to think with anticipation - though I am not fully certain yet) - The values "Txt1", Txt2 etc. in real application would be trading signals such as Buy, sell, B2C and so on. Now, the cell that generates/populates a Buy or a SEll etc - may change to a 1 or -1 later.

In fact, I am glad you asked this question.

To explain a bit - I want to trigger Alerts/MsgBox alerts - when the Buy, Sell values are populated .... since the sheet may be minimized/in the background and I may be busy with something else. The cells have formulas, but since worksheet_change does not recognize formulas -from what I understand - If I can use/apply a Macro to - copy / paste the Value (buy/sell etc) - from Sheet1 to the corresponding cell in Sheet 2 - then that WILL be recognized as an event change - and I can use the changing cell values in sheet2 to trigger the pop up alert.

Now the reason, your question is significant is - because I would prefer that the same cell not trigger alerts repeatedly over the same/old signal - so once/if the cells in sheet1 populate a value other than Txt1, 2, 3 etc, - than with the corresponding values (txt1, 2, 3) being cleared on sheet2 would prevent alerts being triggered from the old signal.

Thank you.