Use The VBA To: Automatically copy and paste values in excel, to create historical table

[Corried]

Excel Fans
I have a situation
Every day I received data from excel web into spreadsheet #1, columns "A2" & "B2" see example

Date	Value
Tue-17-2019	4567.89

Now. I want excel to automatically copy both cells reference and create; "Date & Price" History on spreadsheet # 2. See example

Date	Value
Fri-13-2019	5147.23
Mon-16-2019	2578.05
Tue-17-2019	4567.89

Thanks for your reply. I forget to mention that data is automatically is "Plugin". How can VBA support my concerns?

 

[QuietRiot]

End Sub
[/CODE]

 

[QuietRiot]

I originally had a worksheet change event, but not sure if that will fit your needs? Does B2 get updated after A2 does?

You can add a worksheet change event on your sheet1. Any time B2 gets changed it will copy that data over onto a history sheet for you.
If you've never used VBA before:
Press Alt+F11 (hold Alt key, then press F11 key) from anywhere in Excel. The Visual Basic Editor window will open. Now double click on Sheet1 on your VBA project on the left hand side. Now copy this bit of code below and make changes to the sheet names to whatever you're calling your sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsMain As Worksheet, wsHistory As Worksheet
    Dim NextRow As Long
    
    ' Change these to your worksheet names
    Set wsMain = Sheets("Main")
    Set wsHistory = Sheets("Date and Price History")
    
    ' Any time the main worksheet B2 changes
    If Target.Address = "$B$2" Then
        ' Get the next row on history sheet
        NextRow = wsHistory.Cells(Rows.Count, 2).End(xlUp).Row + 1
        ' Copy values
        wsHistory.Range("A" & NextRow).Value = wsMain.Range("A2").Value
        wsHistory.Range("B" & NextRow).Value = wsMain.Range("B2").Value
    End If

End Sub

 

[Corried]

Thanks for your suggestion and your advice. I am not sure if you can help me. I just don't know. I have followed your instructions. Also I have change the name of "sheet 1" to "Main" and "sheet 2" to "Data and Price History".

The problem is: I fill the code into VBA, But when I click on the "run" Icon. A dialog box open, which prompt me to fill-out "Macro" requirements. Not sure what to do from here.

 

[QuietRiot]

You don't have to click run. Close the Visual Basic Editor and change the value from 110 to something else and see if it then records it on the history tab

 

[Corried]

Wow. It works. It works when you type the values in cell B2 ONLY.

Now. Here is things get tasty. My concern is this:

1) I received data from excel web automatically. (Click on "Data Tab" then "From Web").
My problem here is this: when data is plug-in at the same time into "sheet 1" in cells "A2 (Dates) & B2 (Values), "sheet 2" don't get fill.

How do I work around that?

and

2) This is going to get tasty. Now This is the plan...
On "sheet 1". Receiving multiple "data from excel web" automatically on "Column A (Dates)" & "Column B (Values)".

How can I copy each group data from "Worksheet 1" and create a historical list on each "worksheet 2-5" an so on...?

Example:
A1 & B1 (Worksheet 2)
A2 & B2 (Worksheet 3)
A3 & B3 (Worksheet 4)
A4 & B4 (Worksheet 5)
A5 & B5 (Worksheet 6)
And so on, and so on.

What is the solution?
How can this be accomplish?

Thanks in advance...

 

[QuietRiot]

Hmm what if instead we use Workbook_SheetChange. So this bit of code would not go in Sheet1 and would go in "ThisWorkbook" instead. Now if any worksheet gets changed and their A2 and B2 both contain data then on the history tab it will put the date, value and the sheet name changed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)

    Dim wsHistory As Worksheet
    Dim NextRow As Long
   
    Application.EnableEvents = False
   
    ' History tab sheet:
    Set wsHistory = Sheets("Date and Price History")
   
    Application.EnableEvents = False
   
    ' If the sheet changed is not the history tab then..
    If Sh.Name <> "Date and Price History" Then
        NextRow = wsHistory.Cells(Rows.Count, 2).End(xlUp).Row + 1
        ' If both A2 and B2 contain data then
        If Len(Range("A2").Value) > 0 And Len(Range("B2").Value) > 0 Then
            ' Copy values
            wsHistory.Range("A" & NextRow).Value = Range("A2").Value
            wsHistory.Range("B" & NextRow).Value = Range("B2").Value
            ' Include Sheet name
            wsHistory.Range("C" & NextRow).Value = Sh.Name
        End If
    End If

    Application.EnableEvents = True

End Sub

 

[Corried]

The photo example above shows the the code you've recommended and it works perfectly. On the right hand side the data was plugin directly from excel web into worksheet.

Now. Base on the example above. I want to create "Each Worksheet, Historical Data" that represents each "country" In "sheet3" as above example.

What are the steps to achieve this gold?

Thanks again for your swift reply

 

[QuietRiot]

I'm sorry mate you've lost me.

 

[Corried]

I have a plan to solve this problem I am trying to create. I want you to feel comfortable rejecting the plan and feel free to give me your ideas.

Can the above code be modify to copy multiple rows in "Sheet1" ONLY.

Then

Paste, each row values into separate "worksheets" to create historical data.

The example color codes below shows:
(Yellow) Sheet1: A1 & B1 values paste into Sheet2 A1 & B1
(Green) Sheet1: A2 & B2 values paste into Sheet2 A2 & B2
(Blue) Sheet1: A3 & B3 values paste into Sheet2 A3 & B3
etc.