Transfer dynamic data from one spreadsheet to another and keep it static

Hi,

When you select one of the drop-down values of "Country name" in cell [A1] of the [Sheet2] worksheet (Uruguay, US, Germany, or India) do you wish the previously stored values (on [Sheet3]) to be reinstated so you can maintain/amend/add to the existing content?

Also, what would you like to do to trigger the copying of the data on [Sheet2] to [Sheet3]?

If the change of "Country name" triggers the copying of the set of data from [Sheet2] to [Sheet3] (in the relevant location for the currently selected "Country name"), how do you indicate data should be written without changing the "Country name"?

Should a button be added such as [Save Data], or prior to closing the workbook (or changing the active worksheet) indicate the present set of data should be copied from [Sheet2] to the appropriate location in [Sheet3]?

Hi,

Many thanks for the response.

I'm not sure which is more easier.

1. Once a country is selected in Sheet2, data is populated and like you said, a save button is pressed which copies all data for that country (including the 3 types - 20,40,refrigerated) into sheet3. Then when a different country is selected and save is pressed, data for this country is copied into Sheet3 below the previous country data. So on and so forth. But, this may cause a risk of duplication of countries if the user clicks the button more than once for the same country isn't it?

2. The second method is to have the data for all countries (except the manual entries in column E) in the dropdown list, in Sheet 3. What I would like is that once we select a country on Sheet2 and populate the data, including the manual entries, the save button would copy only the manual entries for that particular country from column E to column E on Sheet3 as per the country.
So basically, it is like an IF statement, where we would say 'If Country in Sheet2 is Uruguay, then copy column E values for each type (20, 40, refrigerated) and find Uruguay on Sheet3 and enter these values in column E as per each type.

Once again many thanks for your help.

Ok. So now I have gotten around to a temporary solution. I have used Macros to copy the entire data into another worksheet. However, now the problem that I face is that if I change the data for a country twice, both data sets get copied on the next sheet, while I only the latest data-set for a particular country to be copied to the next sheet. That is, if the data for a particular country is updated more than once, the previously copied data on the other sheet for that country alone should be overwritten with the new data. Please help!

This is all that I have for now:

Private Sub CommandButton1_Click()
ActiveSheet.Range("A1:F44").Copy
With Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial xlPasteFormats
ActiveSheet.Range("A1:F44").Copy
With Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets("Sheet2").Activate
End With
End With
End Sub
Sample.xlsm

Hi,

"1. Once a country is selected in Sheet2, data is populated and like you said, a save button is pressed which copies all data for that country (including the 3 types - 20,40,refrigerated) into sheet3. Then when a different country is selected and save is pressed, data for this country is copied into Sheet3 below the previous country data. So on and so forth. But, this may cause a risk of duplication of countries if the user clicks the button more than once for the same country isn't it?"

and

"2. The second method is to have the data for all countries (except the manual entries in column E) in the dropdown list, in Sheet 3. What I would like is that once we select a country on Sheet2 and populate the data, including the manual entries, the save button would copy only the manual entries for that particular country from column E to column E on Sheet3 as per the country.
So basically, it is like an IF statement, where we would say 'If Country in Sheet2 is Uruguay, then copy column E values for each type (20, 40, refrigerated) and find Uruguay on Sheet3 and enter these values in column E as per each type."

(Combined response)

We can find the existing data for the applicable Country on [Sheet3], & replace it or, if not found, add to the existing content on [Sheet3].

Yes, a button to commit the data is a good idea, but code will need to be implemented in case the user changes/adds data on [Sheet2] & does not click the button before closing the workbook.

Flagging that a change has been made, without committing the data to [Sheet3], should prompt the user as a reminder before the workbook is closed.

Also, if the workbook is saved (prior to closure, or just part of normal usage), should the data be automatically committed to [Sheet3]?  Upon closure, yes, but perhaps not if the workbook is simply saved & usage continues.

Given your most recent comment, have you changed the format of the workbook since you last added an attachment to the thread?

It looks like you have added your own button, at the very least, recently, but also renamed the worksheets; [Sheet3] no longer exists, for example.

Is it worthwhile providing a new copy of your current version, then I can look at updating it to find/update or find/add data to [Sheet3] as required?

Hi,

Many thanks. Yes I have found a temporary solution. But, the overwriting condition as I mentioned before is something I'm struggling with. Please find the latest file attached.

Vipin
Sample.xlsm

Sorry for the delay in responding. Many thanks for this. I will come back in case there are any concerns. Once again, thanks for all your time!

Thank you.

Please do not forget to close the question if/when you are happy with the solution provided, rather than just returning if you have any concerns.