If Target.Address change then...automaticaly have to but not working.

[Marcoteo]

Hello everyone, I'm new member and I'm new in this Excel Macro World.
Is a week that i trying to do something with VB macro, I searched and read everywhere, also here (i think is best forum this one) , to find the solution for my problem but trying this and that nothing look working, so, the last solution, here we are.

So, my situation :
I have two Worksheets, and i need to copy the data wrote in the ws 1 (called wsMain) to the second ws (called wsHistory).
In wsHistory i want create a table formed to 4 colums, any time i copy 4 data from wsMain i have a new row in wsHistory.

The copy from wsMain i want that happen when the value of a single cell ("E2") change.
In the E2 i have formula (=E3) with no format (General Format), and E3 is (=MOUNTH(TODAY()). My target before was directly E3 but i saw than not worked.

Ok so this is my VB macro

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsMain As Worksheet, wsHistory As Worksheet
Dim NextRow As Long

' Cambia nome Main con la pagina in cui viene scritta la tabella
Set wsMain = Sheets("DatiEsterni")
Set wsHistory = Sheets("Storico&Grafici")

' Ogni volta che la cella Target cambia
If Target.Address(0, 0) = "E2" Then
' Crea una nuova riga
NextRow = wsHistory.Cells(Rows.Count, 4).End(xlUp).Row + 1
' E copia questi valori nella nuova riga
wsHistory.Range("A" & NextRow).Value = wsMain.Range("D2").Value
wsHistory.Range("B" & NextRow).Value = wsMain.Range("B7").Value
wsHistory.Range("C" & NextRow).Value = wsMain.Range("B8").Value
wsHistory.Range("D" & NextRow).Value = wsMain.Range("B9").Value
End If

End Sub


All working good but the problem is that :

1 - He don't copy the 4 datas automaticaly when E2 change, but i have to select E2 and push Enter. And this is not what i want because i would like that he copy automaticaly when the mounth change.
2- Another thing i would like do ,but online solutions looks not working for me, is that in wsHistory where i want create database when he copy from wsMain he put the result under the rows wrote before and I tryed in all ways to shift old result and put the new row upper the others.

 

[offthelip]

The problem is that the worksheetchange event is NOT triggered when the today() function updates. worksheet change is only trigger when you type something different into the worksheet, not just on a recalculation. An alternative is to change the way that you are using E2 and E3,
if you leave E3 as :
=MONTH(TODAY())
and leave E2 blank.
then use the worksheet activate event to check whether E3 is equal to E2 if it isn't call a sub to update the history sheet and also update E2 with the latest value. To do this put this code in the WsMain worksheet code:

Private Sub Worksheet_Activate()
If Range("e2") <> Range("e3") Then
 Call copydata
 Range("e2") = Range("e3")
End If
End Sub

then create an ordinalry module and put this code into it:

Sub copydata()
Dim wsMain As Worksheet, wsHistory As Worksheet
Dim NextRow As Long

' Cambia nome Main con la pagina in cui viene scritta la tabella
Set wsMain = Sheets("DatiEsterni")
Set wsHistory = Sheets("Storico&Grafici")

' Ogni volta che la cella Target cambia
' Crea una nuova riga
NextRow = wsHistory.Cells(Rows.Count, 4).End(xlUp).Row + 1
' E copia questi valori nella nuova riga
wsHistory.Range("A" & NextRow).Value = wsMain.Range("D2").Value
wsHistory.Range("B" & NextRow).Value = wsMain.Range("B7").Value
wsHistory.Range("C" & NextRow).Value = wsMain.Range("B8").Value
wsHistory.Range("D" & NextRow).Value = wsMain.Range("B9").Value

End Sub

 

[Osvaldo Palmeiro]

This will run automatically when a new month arrives.

Private Sub Worksheet_Calculate()
 If [E2] = [E3] Then Exit Sub
 Application.ScreenUpdating = False
 [E2] = [E3]
 With Sheets("Storico&Grafici")
  .Rows(2).Insert
  [D2].Copy
  .[A2].PasteSpecial xlValues
  [B7:B9].Copy
  .[B2].PasteSpecial , Paste:=xlValues, Transpose:=True
 End With
End Sub

 

[Marcoteo]

Scusami Osvaldo, I'm so sorry but is not working, maybe is me that i wrong somenthing...I tryed your codes and I tryed to modify them but nothing to do.

I don't understand why you put in relation E2 and E3, maybe is me that I explaned bad?

I want explane you again for be sure we speak about same thing, i told you I have two worksheet, in the main i have some data that i want copy in the History ws.
In the main i wrote also the actual date, i take from the actual date only the mounth in E3, and in E2 i copy the value of E3.
I did the copy in E2 because maybe is better if Target.Address is only a number without any format and not the formula of E3 (=MOUNTH(..)).
So maybe have no sense write E2= <>E3 because is always true no? I didn't understood that.

So for me in the end every thing working, when E2 change value he copy automaticaly in wsHistory the row whit the data i want.
The problem is that he don't do it when E2 change automaticaly when the mounth change, so I have to push always enter in E2 for have a copy in the other page.

This is my principal problem and the important one. I hope you understand now my problem also because i don't now how to solve it.

Anyway.
I saw that probably you solved me my second problem (the new row copied that go up on database) with this code :

With Sheets("Storico&Grafici")
.Rows(2).Insert
[D2].Copy
.[A2].PasteSpecial xlValues
[B7:B9].Copy
.[B2].PasteSpecial , Paste:=xlValues, Transpose:=True

but i try and not worked immidiatly, but I think is correct and I have to think and test more about in nex days!!

OOH for moment thanks so much for your answer and the rapidity,
If "burocrazy is rapid the half of your answer" Then
World would be better place

 

[Osvaldo Palmeiro]

Ciao, Marco.
Let's start from the following: E3 has the formula = MONTH (TODAY)), which currently returns 11, and E2 is empty.

Next time the sheet will be calculated, the Calculate event will fire and then the code I passed will check if E2 = E3. As it will be False, then the code will replicate the data in the target sheet, as you asked for, and it will put in E2 the value equal from E3's formula , that is, from that point we'll have E2=11 and E3=11 as well.

Thus, the next times the sheet will calculate, the code will not perform any action because E2=E3 will be True. It will be that way until November 30th.

However, when opening the file first time from next December 1rst, the formula in E3 will return 12, and so E2=E3 will be False, then the cycle will restart, by the code replicating the data and making E2=E3 again.

In order to check that, you could do the following test: in any empty cell type the formula =E2, then type in E2 any value different from the current result of E3, which is 11, put for example 9 and then see what the code will perform.

 

[offthelip]

This will run automatically when a new month arrives.

Private Sub Worksheet_Calculate()
 If [E2] = [E3] Then Exit Sub
 Application.ScreenUpdating = False
 [E2] = [E3]
 With Sheets("Storico&Grafici")
  .Rows(2).Insert
  [D2].Copy
  .[A2].PasteSpecial xlValues
  [B7:B9].Copy
  .[B2].PasteSpecial , Paste:=xlValues, Transpose:=True
 End With
End Sub

Firstly if you turn off Screenupdating you MUST turn it back on again at the end of the subroutine, otherwise excel isn't going to work properly at all.
Secondly the code I posted, which basically works in exactly the same way, uses the worksheet activate event. This is triggered every time you select the worksheet. So to trigger it select another workhseet and then back to the worksheet. Since when you open workbook you have to activate a worksheet to look at it, this will triggered automatically when today() returns a value which is not the same as the value in E2 as soon as you look at the worksheet
I did think about using the worksheet calculate event however this can be a problem because you can activate a worksheet without triggering a recalculation. I suspect the today() function does not trigger a recalculate event unless some other cell is using the value from that cell. So I suspect this is why you code still doesn't update automatically.
So can I suggest to the OP (Osvald) that you try the code in my post 2 and see if that works for you