copy once to the last empty cell of a destination sheet

[erfo]

Hi; I do appreciate any help on the following vba macro problem:
I have
The source sheet has different data on A3, A4, A5; each data changes daily.
Destination sheet have D, G and J colums to be filled with the data from A3, A4 and A5 of the source sheet;
(E,F,H,I,J AND K have some calculations They are not my concern; they should not be touched).
when copies, it should copy to the last empty cell of the second sheet ONLY İF the source value is changed.
important NOTES: (a) I place/insert the vba code to the source sheet. (b) I use 3 if statements because I want each data copied individually (because I get some data in different hours of the day).
I have the following macro but it does not work as I want:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsSource As Worksheet, wsDestination As Worksheet
Dim NextRow As Long
Set wsSource = Sheets("g1")
Set wsDestination = Sheets("GLD")
If Target.Address = "$A$3" Then
NextRow = wsDestination.Cells(Rows.Count, "D").End(xlUp).Row + 1
wsDestination.Range("D" & NextRow).Value = Range("A3").Value
End If
If Target.Address = "$A$4" Then
NextRow = wsDestination.Cells(Rows.Count, "G").End(xlUp).Row + 1
wsDestination.Range("G" & NextRow).Value = Range("A4").Value
End If
If Target.Address = "$A$5" Then
NextRow = wsDestination.Cells(Rows.Count, "J").End(xlUp).Row + 1
wsDestination.Range("J" & NextRow).Value = Range("A5").Value
End If
End Sub

Here are the images of Sheet1 (g1) and Sheet2 (GLD):

Name	Nav
Dz Nav	0.152449
Gp Nav	9.883455
Pv Nav	35.0395

date	expl	expl	Dz Nav	ytd	daily %	Gp Nav	ytd	daily	Pv Nav	ytd	daily
			0.02405	7.34%	0.00%	9.40035	40.54%	0.00%	0.167906	29.23%	0.00%
			0.02435	8.72%	1.26%	9.40000	40.53%	0.00%	0.184345	41.88%	9.79%
			0.02314	3.28%	-5.27%	9.49791	42.00%	1.04%	0.184345	41.88%	0.00%
			0.02436	8.74%	5.02%	9.51419	42.24%	0.17%	0.184345	41.88%	0.00%
			0.02447	9.22%	0.45%	9.55992	42.92%	0.48%	0.188322	44.94%	2.16%

 

[MARK858]

Thanks a lot for revisions. I tried it, but it completely stopped; Firstly, I run it as is, it didnot do anything. (2) Secondly, I employed "refresh all", it refreshed the data on the g1; but nothing on the GLD. Thirdly, ı tried after removing "Private Sub Workbook_Open()"; nothing happenned; ı typed the data by hand; no success; Fourthly, I tried your previous macro, it worked when i typed the data; but didnot copied to the GLD, fter refreshing the data by using a diffrerent vba macro. I have being using many combinations and other solutions with no success. I am frustrated becuase I spend a lot of time, over a month to solve it. I hope we solve it eventually. Thanks a lot for your kind interest and effort to help.

It works fine for me, see the workbook in the link below, save the file to your desktop (unblock the file if needed), open the file then alter the data in K2-K4, and click refresh all, which updates the data in B3-B5 and transfers the data to the other sheet.

Forum test.xlsm | Powered by Box

app.box.com

 

[erfo]

It works fine for me, see the workbook in the link below, save the file to your desktop (unblock the file if needed), open the file then alter the data in K2-K4, and click refresh all, which updates the data in B3-B5 and transfers the data to the other sheet.

Forum test.xlsm | Powered by Box

app.box.com

Thank you very much again for your time and effort. It works because we enter the data by hand on the table. BUT IT DOES NOT WORK when the data is uploaded, refreshed from an external web source, and renewed on a query table (I tested it). I have a small hope, I wait till tomorrow to see if it will copy.
IT SEEMS THAT it will work by using "Private Sub CalCopy()" and "Private Sub Worksheet_Calculate()" (I read it some place ı donot remember; I tried but ı was not succesful becuase of my very limited knowledge on VBA). Thanks gain.

 

[MARK858]

As I stated the last code that I posted (which is in the workbook that I posted) works for me with the RefreshAll icon on the data tab.
You must open it from a closed workbook to initialize the code, then you use the RefreshAll as many times as you like to do the updates, it does not require any running or being called by another macro.
The only bit that got done by hand was the filling in of the 2nd table which is just there to simulate the source data, the code does nothing with that table it is purely source data. The data transfer is triggered by the RefeshAll and gets the data from the table in column B

 

[erfo]

As I stated the last code that I posted (which is in the workbook that I posted) works for me with the RefreshAll icon on the data tab.
You must open it from a closed workbook to initialize the code, then you use the RefreshAll as many times as you like to do the updates, it does not require any running or being called by another macro.
The only bit that got done by hand was the filling in of the 2nd table which is just there to simulate the source data, the code does nothing with that table it is purely source data. The data transfer is triggered by the RefeshAll and gets the data from the table in column B

I quess I confuse query table name and the index you want me to replace "(2) in your macro.
Your note: " Set QT = Sheets("g1").ListObjects(2).QueryTable 'CHANGE THE 2 TO MATCH YOUR TABLES INDEX". It tells me to change the 2 inthe (2) with my tables index. I replace the 2 with the query name of my table (such as, "Table1"). I have column index that provides a list such as 0, 1, 2. I do not know what the "table index." I never needed it beyond column index. I need your help on my query table index that you want. Thank you.

 

[erfo]

I found the query table index. It is (1). thanks. I have a workbook with about 20 worsheets, two of the worksheet are g1 and GLD. I copied the macro to the ThisWorkbook of the workbook. It didnot work. I put it in the g1 sheet, it did not work. It works only if I have a workbook that has only two worksheets (such as g1 and GLD sheets). Thanks again.

 

[MARK858]

First of all it is workbook level code and so it needs to be in the ThisWorkbook module.
My Workbook defaults to 4 sheets and it doesn't affect the code running for me at all.

Edit: I have just created 15 more sheets and put a table on every sheet and it still transfers the data when I do a RefreshAll from g1 to GLD

 

[erfo]

Thank you for your kind effot and help. I will try few more time this weekned.

 

[erfo]

The macro below pastes the data from the query table to the destination sheet. It also prevents further pasting the same value unless value is changed. The macro needs properly revision that I do not have enough knowledge. I just used my logic and "try and error" method and come up with the macro below. It works, but i bet it definitely needs revision. I do appreciate any heip. Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsSource As Worksheet, wsDestination As Worksheet
Dim NextRow As Double
Set wsSource = Sheets("eu1")
Set wsDestination = Sheets("EUR")

'FİRST PART
If Target.Address = "$B$3" Then
NextRow = wsDestination.Cells(Rows.Count, "D").End(xlUp).Row
If wsDestination.Range("D" & NextRow).Value <> wsSource.Range("B3").Value Then _
wsDestination.Range("D" & NextRow + 1).Value = wsSource.Range("B3").Value
'I didnot know how to prevent macro to multiple copy of the same value when source data refreshed
'That is why i repeated the if command and it worked
If wsDestination.Range("G" & NextRow).Value <> wsSource.Range("B4").Value Then _
wsDestination.Range("G" & NextRow + 1).Value = wsSource.Range("B4").Value

If wsDestination.Range("J" & NextRow).Value <> wsSource.Range("B5").Value Then _
wsDestination.Range("J" & NextRow + 1).Value = wsSource.Range("B5").Value
End If

'ATTN: the above part does not paste the new data in the query table to the destination.

'SECOND PART blow pastes the new data to destination
NextRow = wsDestination.Cells(Rows.Count, "D").End(xlUp).Row
If wsDestination.Range("D" & NextRow).Value <> wsSource.Range("B3").Value Then _
wsDestination.Range("D" & NextRow + 1).Value = wsSource.Range("B3").Value
'it prevents multiple copy of the same value when source data refreshed
If wsDestination.Range("G" & NextRow).Value <> wsSource.Range("B4").Value Then _
wsDestination.Range("G" & NextRow + 1).Value = wsSource.Range("B4").Value
''it prevents multiple copy of the same value when source data refreshed
If wsDestination.Range("J" & NextRow).Value <> wsSource.Range("B5").Value Then _
wsDestination.Range("J" & NextRow + 1).Value = wsSource.Range("B5").Value

End Sub
Sub turningon()
Application.EnableEvents = True
'ActiveWorkbook.ForceFullCalculation = True
End Sub

 

[erfo]

Please ignore this threat, because I gave up. I thank you for your help.