asked on

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.

Russell Fox

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

ScottBarnes

ASKER

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 Fox

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

ScottBarnes

ASKER

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,

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ScottBarnes

ASKER

This is perfect! I really appreciate your help.