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

 

[Fluff]

How are you adding the New sales months?

 

[Christian S]

I use a second userform that adds a new column to a name range ("Months")

 

[Fluff]

Are those columns being inserted before column number 250?

 

[Christian S]

Yes the data will never reach the 250th column so i just added it there randomly

 

[Fluff]

In that case I don't understand what you are saying, as this formula

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

should average all of the data in columns B:IN

 

[Christian S]

At the moment it only averages the data that is there before i run the macro. An example would be if i was running a test on my sheet and had some mock data such as

June 2019 - 300 sold
July 2019 - 400 sold

Then i run the macro and the =average gives 350 as it should. But lets say i add a new month (August 2019 - 500 sold.) The result in the masterlist should be 400, but stays as 350. And because i increment the row each time there is now way to update by running the macro again either.

 

[Fluff]

If the product already exists then surely you should be adding the new sales to the existing row, not creating a new row for it.

 

[Christian S]

I do add to the same row. The new row is for any new products. But once the product is added, the macro moves the active row down to prepare for new products

 

[Fluff]

Are you entering the data like

Book1
	A	B	C	D	E	F	G	H	I	J
1		Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19
2	Apples	100	500		200
3	Oranges		300	450			100	600
4	Pears			200	300	700		150
List