Excel vba to copy new data to the [MAF_History] tab

I have tab called Dashboard that has recent data and I also have a tab called MAF History containing the same date for older time frames.  Once the [R] “Watts Per Kilogram” on the dashboard page goes from 0.00 to a value higher than 0.00 I would like to have it copied to the MAF_History tab so that it can be included in the chart as long as it isn’t already there.

A combination of columns [K] "Date" / [O] "Weight" / [P] "HR" / [Q] "Watts" on [Dashboard] would make the unique key to be matched to columns [ B ] "Date" / [C] "Weight" / [D] "HR" / [E] "Watts" on [MAF_History] tab.  If the new combination is not on the [MAF_History] tab it would get copied over.

I did try to record a macro in excel, but I don’t think the complexity can be recorded in a macro and hence the need for help from an Expert.
ScottBarnesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Russell FoxDatabase DeveloperCommented:
It depends on how the Watts/KG cell is updated. Something like this should work if you're in that cell and press <Enter>:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Name= "WattsPerKG" AND Target.Value > 0.00 Then
		
		Application.ScreenUpdating = False
		Dim dashboard As Worksheet
		Set dashboard = ThisWorkbook.Worksheets("Dashboard")
		dashboard.Copy ThisWorkbook.Sheets(Sheets.Count)
		Application.ScreenUpdating = True

	End

End Sub

Open in new window

ScottBarnesAuthor Commented:
The Watts/KG cell is a formula.  I added an updated attachment that shows the formula.  When I try to use the code you provided, it errors out on this line of code:

If Target.Name = "WattsPerKG" And Target.Value > 0# Then
Charting.xlsm
Russell FoxDatabase DeveloperCommented:
That was assuming you had a single result cell and you'd named it WattsPerKG. Is the idea that you or someone else would enter values in the yellow area (weight, hr, watts) by date, and that would update the formula in the Watts Per Kilogram column? And then if that goes above zero you want the MAF_History sheet copied? If so, this might work. I also added "copied" into column S so that it won't keep making copies every time you hit <Enter> from column Q:
    ' 17 = Q
    If Target.Column = 17 And Range("R" & Target.Row).Value > 0 And IsEmpty(Range("S" & Target.Row).Value) = True Then
        
        Application.ScreenUpdating = False
        Dim dashboard As Worksheet
        Set dashboard = ThisWorkbook.Worksheets("MAF_History")
        dashboard.Copy ThisWorkbook.Sheets(Sheets.Count)
        Range("S" & Target.Row).Value = "Copied"
        Application.ScreenUpdating = True
    
    End If

Open in new window

Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

ScottBarnesAuthor Commented:
Exactly, if someone enters values in the yellow area, columns [K] "Date" / [O] "Weight" / [P] "HR" / [Q] "Watts", the Watts Per Kilogram formula would get updated and if that data is not already on the MAF_History tab, it would get copied over and the chart would update.

Thank you,
Ejgil HedegaardCommented:
Try this
Charting.xlsm

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
ScottBarnesAuthor Commented:
This is perfect!  I really appreciate your help.
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.