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?

 

[Corried]

I made error. on the last comment: Here are the correct info. below. Thanks in advance.

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 Sheet3 A1 & B1
(Blue) Sheet1: A3 & B3 values paste into Sheet4 A1 & B1
etc.

 

[QuietRiot]

It's confusing because it's completely different now. You want it to grab columns A, B and C then put that data in your Date and Price history sheet?

 

[Corried]

I made a couple errors before. Hope you understand what I am trying to explain below.

I am working on a plan and I want you to feel comfortable rejecting the plan and feel free to give me your ideas, that's best for all of us.

My problem here is this:

On "sheet 1". I am receiving multiple "data from excel web" AUTOMATICALLY on "Column A (Countries)", "Column B (Dates)" and "Column C (Values)"

In "sheet 1", how can I copy individual row at the same time and paste each row values into separate "worksheets" to create historical data.

Or to create a historical list for each country.

See example below.

Sheet1: A1, B1 & C1 values paste into Sheet2 A1, B1 & C1
Sheet1: A2, B2 & C2 values paste into Sheet3 A1, B1 & C1
Sheet1: A3, B3 & C3 values paste into Sheet4 A1, B1 & C1
Sheet1: A4, B4 & C4 values paste into Sheet5 A1, B1 & C1
Sheet1: A5, B5 & C5 values paste into Sheet6 A1, B1 & C1

Also See Pic. Example. The color codes represents where the historical data is storing.

Every new data is paste in the next available row.
How can you help me?

PLEASE NB: SHEET 3 ON THE PHOTO SHOULD HAVE BEEN SHEET 1 IT'S AN ERROR.

NB. once data is transferred I am using data to create a percentage change and also create a chart as well

 

[QuietRiot]

This will use a sheet called "Data From Web". It will go through the list and place the country, date and price on a sheet with the name of the country and on the next available row. If a sheet with the country name doesn't exist it will create it first. This is it's own separate module and not in workbook. If you want it to be a change event you will have to figure that out on your own using the previous examples.

Sub UpdateHistory()
    Dim wsData As Worksheet, wsCountry As Worksheet
    Dim LastRow As Long, NextRow As Long, i As Long
    Dim Country As String
    Dim PriceDate As Date
    Dim Price As Double
    
    Application.ScreenUpdating = False
    
    ' The sheet that gets data dump
    Set wsData = Sheets("Data From Web")
    ' Last row in that sheet
    LastRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To LastRow
        Country = wsData.Range("A" & i).Value
        PriceDate = wsData.Range("B" & i).Value
        Price = wsData.Range("C" & i).Value
        
        ' If worksheet exits
        If WorksheetExists(Country) = True Then
            Set wsCountry = Sheets(Country)
            NextRow = wsCountry.Cells(Rows.Count, 1).End(xlUp).Row + 1
            wsCountry.Range("A" & NextRow).Value = Country
            wsCountry.Range("B" & NextRow).Value = PriceDate
            wsCountry.Range("C" & NextRow).Value = Price
        Else
            ' Create new country sheet
            wsData.Range("A1:C1").Copy
            Worksheets.Add(After:=Sheets(Sheets.Count)).Name = Country
            ActiveSheet.Cells(1, 1).PasteSpecial
            ActiveSheet.Range("A2").Value = Country
            ActiveSheet.Range("B2").Value = PriceDate
            ActiveSheet.Range("C2").Value = Price
        End If
    Next
    
    Application.CutCopyMode = False
    Sheets("Data From Web").Select
    
    Application.ScreenUpdating = True
        
End Sub


Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean
    Dim sht As Worksheet

    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

 

[Corried]

It really works. Hummm.

The question is:
The second to last code you have recommended... If I use the code 50 times. Each one represent a worksheet, How can it work?

Thanks in advance

See below adjustments I have made

VBA Code:

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("Data From Web")

Application.EnableEvents = False

' If the sheet changed is not the history tab then..
If Sh.Name <> "Country, PriceDate and Price History" Then
NextRow = wsHistory.Cells(Rows.Count, 2).End(xlUp).Row + 1
' If both A2, B2 and C2 contain data then
If Len(Range("A2").Value) > 0 And Len(Range("B2").Value) > 0 And Len(Range("C2").Value)> 0 Then
' Copy values
wsHistory.Range("A" & NextRow).Value = Range("A2").Value
wsHistory.Range("B" & NextRow).Value = Range("B2").Value
wsHistory.Range("C" & NextRow).Value = Range("B2").Value
' Include Sheet name
wsHistory.Range("D" & NextRow).Value = Sh.Name
End If
End If

Application.EnableEvents = True

End Sub

 

[Corried]

QuietRiot

I really appropriate your support, patience and your love for excel. program. You really are devoted to help others with their problems and concerns. I am truly grateful for helping me to the next level.

The challenge I am facing currently is this:

Note: The above "code" did work for all worksheet tabs you have recommended. However, the problem I have is this.
When new data is being uploaded. the rest of the worksheets aren't updated.

How can macros update automatically?

With two more pieces of the puzzle. the work is completed.
I have learn a lot through your patience, passion you're demonstrated.

Thanks again for your support.

 

[QuietRiot]

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

    Application.EnableEvents = False
    
    ' If data from web sheet updatd then..
    If Sh.Name = "Data From Web" Then
        ' Call the other code
        Call UpdateHistory
    End If
    
    Application.EnableEvents = True

End Sub

 

[Corried]

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

    Application.EnableEvents = False
   
    ' If data from web sheet updatd then..
    If Sh.Name = "Data From Web" Then
        ' Call the other code
        Call UpdateHistory
    End If
   
    Application.EnableEvents = True

End Sub

I am getting and error message when data is paste in

 

[Corried]

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

    Application.EnableEvents = False
   
    ' If data from web sheet updatd then..
    If Sh.Name = "Data From Web" Then
        ' Call the other code
        Call UpdateHistory
    End If
   
    Application.EnableEvents = True

End Sub

I am stuck. I am not sure what I can do from here, I'm on my second to last step and it seems like a daunting task. Not sure if anyone can recommend my challenges to any excel pros, that I am facing.

It seems like I have spoken a lot and it feels like I am crossing my boundaries and I have no limit. If it is so. I am very sorry. I am in your debt. How do you want me to proceed?

 

[QuietRiot]

Remember Private Sub Workbook_SheetChange code needs to go under "ThisWorkbook". The other code can be in it's on module (Module1). Insert > Class Module