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

Hi all,

I have a sheet which contains dynamic data (sheet2 in the attached workbook), refreshed frequently. Data displayed relates to a specific country, selected from the dropdown in A1. The only manual entries one can make are in column E. The rest of the calculations are based on the manual entries.

Now I want to transfer this data on another sheet (sheet3), so that information for the countries are stored one after the other, in the same format as sheet2.

I am unfamiliar with Macros/VBA so any help in bringing about the solution would be highly appreciated.


Many thanks in advance!

Follow these steps

• Right click on sheet name
• Move or copy
• (check) Create a copy
• OK

Hi zbor,

Thanks for this. Although in the example I showed only 4 countries in the dropdown list, there might be many more, over 20 or so. I was looking at an automated solution for this.

So, this wouldn't be possible even using Macros/VBA, is that what you're hinting at?

Many thanks.

For your solution ideal would be to use Pivot table.
However, you should reorganize your Data sheet for that and get used to it.
once you get used to it everything would be much simplier.

And you could also split your countries per sheets.

zbor, unfortunately, the design of the table cannot be changed. There needs to be dropdown list on the first sheet from where companies are selected and that country-specific data needs to stored in the other sheet without having to manually copy data for each country. I would've done that if there were just handful of countries but like I said the list could be very long.

Does this call for a "Bump"?

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

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Sorry for that zbor, Please find the code below:

Please Login or Register  to view this content.

Nobody there to help me with this?