VBA Self updating data?

[Christian S]

Hi Everyone,

I was hoping for some help with a piece of code that keeps tripping me up. I work for a store and have lots of pages relating to different kinds of product. I'm attempting to add them all to one "master list" that contains data for a pivot table. New products get added via a userform that allows input of the product name and the first month that the product was sold (months are pre-loaded into a combobox and new months can be added when needed.) These new product are automatically added to the bottom row of the master list. Data will be inputted for each product in the list at the end of each month, and the master list contains an "average sold per month" column for this data. However, once the macro has been run and a product added it will only give an average of the data already included. Any new sales months added will not affect the data in the master list or resulting pivot table. Does anyone know a way to set this to update as new data is entered? I've included my userform code below for reference. Many thanks in advance.

Private Sub btn_AddProduct_Click()


Dim i As Integer, CurrentSheet As String, yrow As Integer, AvgSales As Variant, FirstMonth As String, FirstMonthSales As Variant


CurrentSheet = ActiveSheet.Name


Call FindNextCell
ActiveCell.Value = txt_Name


yrow = ActiveCell.Row


FirstMonth = combo_FirstMonth.Value
 ycol = (Range("Episodes").Find(FirstMonth).Column)


FirstMonthSales = Application.InputBox("How Many Sold In First Month", "First Month Sales", , , , , , 1)
 Cells(yrow, ycol).Value = FirstMonthSales


Cells(yrow, 250).Formula = "=average($B$" & yrow & ":$IN$" & yrow & ")"
  
 AvgSales = Cells(yrow, 250).Value


With Worksheets("Pivot Data").Select
Dim FirstCell As String
Dim j As Integer
FirstCell = "A1"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop


xrow = ActiveCell.Row


Cells(xrow, 2).Value = txt_Name.Value
Cells(xrow, 1).Value = CurrentSheet
Cells(xrow, 3).Formula = AvgScore


End With


Worksheets(CurrentSheet).Select




End Sub

 

[Christian S]

Yes spot on. And if i ran the code in the original post with that data, apples would be added to my pivot data sheet master list with an average of 266.66. However, if i then added oct-19 with a total of 400, the result in column 250 would change to 300. However, my masterlist 'aveage per month' column would still show 266.66 and so would my pivot table.

 

[Fluff]

How is the "average per month" column getting updated?

 

[Christian S]

Well the problem is that it isn't updating. But these lines of my code should mean that it is...

Cells(yrow, 250).Formula = "=average($B$" & yrow & ":$IN$" & yrow & ")"
AvgSales = Cells(yrow, 250).Value

Cells(xrow, 3).Formula = AvgSales

 

[Fluff]

In the code you posted originally you have

Cells(xrow, 3).Formula = Avg[COLOR=#ff0000]Score[/COLOR]

 

[Christian S]

I know apologies, score was a typo and should have said sales

 

[Fluff]

In that case if the formula is updating correctly, I can't see why the master wouldn't.
You can try making these two changes

AvgSales As [COLOR=#ff0000]Double[/COLOR]
Cells(xrow, 3).[COLOR=#ff0000]Value[/COLOR]= AvgSales