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!
Example.xlsx
Vipin VishnuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
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]?
0
Vipin VishnuAuthor Commented:
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.
0
Vipin VishnuAuthor Commented:
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
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

[ fanpages ]IT Services ConsultantCommented:
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?
0
Vipin VishnuAuthor Commented:
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
0
[ fanpages ]IT Services ConsultantCommented:
Hi Vipin,

I have added code to the attached workbook that retrieves the relevant content from the [Sheet1] worksheet based on the selection of "Country" in cell [A1] of the [Sheet2] worksheet.

Also, clicking the Command Button "saves" the data from [Sheet2] to [Sheet1].

If data is changed for one Country, & a new Country is selected without using the Command Button, a prompt is displayed to warn that the data will be lost allowing the rejection of the change of Country if desired.

There is, presently, no consideration for closing the workbook without clicking the Command Button (if there are any outstanding changes) but, of course, MS-Excel will prompt to save the workbook in any respect.

The only issue here is that upon re-opening the workbook, any non-committed data on [Sheet2] may be lost if a different Country is selected without clicking the Command Button.

Perhaps you can continue the development using the code I have provided if you feel this is a concern.

Thank you for replying to indicate how far this approach is from your expectations.


The code included within the code module for the [Sheet2] worksheet is as follows...

Option Explicit
' ------------------------------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/questions/28701500/Transfer-dynamic-data-from-one-spreadsheet-to-another-and-keep-it-static.html ]
'
' Question Channel: Experts Exchange > Questions > Transfer dynamic data from one spreadsheet to another and keep it static
' Topic Area #1:    [ http://www.experts-exchange.com/topics/ms-excel/ ]
' Topic Area #2:    [ http://www.experts-exchange.com/topics/vb-script/ ]
'
' ID:               Q_28701500
' Question Title:   Transfer dynamic data from one spreadsheet to another and keep it static
' Question Dated:   2015-07-30 12:58 PM
' Question Asker:   Vipin Vishnu
' Asker Profile:    [ http://www.experts-exchange.com/members/vienvy.html ]
' Attachment #1:    [ http://filedb.experts-exchange.com/incoming/2015/07_w31/926213/Example.xlsx ]
' Attachment #2:    [ http://filedb.experts-exchange.com/incoming/2015/08_w32/926740/Sample.xlsm ]
' Attachment #3:    [ http://filedb.experts-exchange.com/incoming/2015/08_w32/926844/Sample.xlsm ]
'
' Solution posted:  4 August 2015 by fanpages
' Expert Profile:   [ http://www.experts-exchange.com/members/fanpages.html ]        [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2015 Clearlogic Concepts (UK) Limited                                                 [ http://NigelLee.info ]
' ------------------------------------------------------------------------------------------------------------------------------------

Private blnWorksheet_Changed                            As Boolean
Private lngErr_Number                                   As Long
Private strErr_Description                              As String
Private Sub CommandButton1_Click()

  Dim objCell                                           As Range
  
  On Error GoTo Err_CommandButton1_Click
  
  Application.ScreenUpdating = False
  
  Set objCell = Worksheets("Sheet1").Columns(1).Find(What:=Worksheets("Sheet2").Range("A1").Value)
     
  If (objCell Is Nothing) Then
     Set objCell = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Offset(1&)
  End If ' If (objCell Is Nothing) Then
  
  If Not (objCell Is Nothing) Then
     Worksheets("Sheet2").Range("A2:F45").Copy Destination:=objCell
     Application.CutCopyMode = False
     
     MsgBox "Data saved.", _
            vbInformation Or vbOKOnly, _
            Worksheets("Sheet2").Range("A2").Value
            
     blnWorksheet_Changed = False
  End If ' If Not (objCell Is Nothing) Then
     
Exit_CommandButton1_Click:

  On Error Resume Next
  
  Set objCell = Nothing
  
  Application.ScreenUpdating = True
  
  Exit Sub
  
Err_CommandButton1_Click:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  MsgBox "ERROR #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name
         
  Resume Exit_CommandButton1_Click

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

  Dim blnGet_Data                                       As Boolean
  Dim objCell                                           As Range
  
  On Error GoTo Err_Worksheet_Change
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  blnGet_Data = False
  
  If Target.Address = [A1].Address Then
     If (blnWorksheet_Changed) Then
        If MsgBox("Existing data has not been saved." & _
                  vbCrLf & vbLf & _
                  "Do you still wish to retrieve data for " & Worksheets("Sheet2").Range("A2").Value & "?", _
                  vbQuestion Or vbYesNo, _
                  ThisWorkbook.Name) = vbYes Then
           blnGet_Data = True
        Else
           Application.Undo
        End If '  If MsgBox(...) = vbYes Then
     End If ' If (blnWorksheet_Changed) Then
     
     If (blnGet_Data) Then
        Set objCell = Worksheets("Sheet1").Columns(1).Find(What:=Target.Value)
     
        If (objCell Is Nothing) Then
           Worksheets("Sheet2").Range("D4:E43").ClearContents
           Worksheets("Sheet2").Range("A2").Value = Target.Value
           Worksheets("Sheet2").Range("A4").Formula = "=IF(ISBLANK(C4),"""",""" & Target.Value & """)"
           Worksheets("Sheet2").Range("A4").Copy Destination:=Worksheets("Sheet2").Range("A4:A15")
           Worksheets("Sheet2").Range("A4").Copy Destination:=Worksheets("Sheet2").Range("A18:A29")
           Worksheets("Sheet2").Range("A4").Copy Destination:=Worksheets("Sheet2").Range("A32:A43")
        
           Worksheets("Sheet2").Range("A4:A43").Copy
           Worksheets("Sheet2").Range("A4").PasteSpecial Paste:=xlPasteValues
        Else
           Worksheets("Sheet1").Range(objCell, Worksheets("Sheet1").Cells(objCell.Row + 43&, "F")).Copy
           Worksheets("Sheet2").Range("A2").PasteSpecial Paste:=xlPasteValues
        End If ' If (objCell Is Nothing) Then
     
        Application.CutCopyMode = False
     
        [A1].Select
     
        blnWorksheet_Changed = False
     End If ' If (blnGet_Data) Then
  Else
     blnWorksheet_Changed = True
  End If ' If Target.Address = [A1].Address Then
  
Exit_Worksheet_Change:

  On Error Resume Next
  
  Set objCell = Nothing
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  
  Exit Sub
  
Err_Worksheet_Change:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  MsgBox "ERROR #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name
         
  Resume Exit_Worksheet_Change

End Sub

Open in new window

Q-28701500.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vipin VishnuAuthor Commented:
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!
0
[ fanpages ]IT Services ConsultantCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.