How to make a macro that updates a summary table with inputs

[neurtron1080]

Hello All,

I am attempting to make a macro that can take inputs from one sheet and update a table on another sheet accordingly.

For example, say I run apple orchards and am attempting to keep up with my company's current retail pricing by orchard and apple type. I have a tab which includes a summary of all the orchards we have, the apples we offer, and their current price of each apple, and I want to update this with a macro say every quarter so that our salespeople always have an updated look at what prices to quote

Example Summary Table (output):

Example Input Sheet:

Of course in this example we only have a few orchards (divisions) and apples (product type) but you could see how updating this for say, 100 different divisions with 10 different product types, row by row, could be tedious.

My goal here is to make a sheet where the end-user (whoever will be updating our pricing once per quarter) can go into the input tab, select the orchard, update the pricing based on product type, and by clicking the button can update the summary tab appropriately.

Bonus: I also have a tab to keep up with historical prices by division/product type as well. If I could get this button to populate a new row to add to the history tab, that would be great!

Thank you,

[#2 · Accepted solution]

@neurtron1080 

Sub apples()

Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim n As Long

For i = 6 To 13
If Cells(i, 13) <> "" Then
m = Application.match(Cells(5, 13), Columns(1), 0)
j = Application.match(Cells(i, 12), Rows(1), 0)
Cells(m, j).Value = Cells(i, 13).Value
Cells(m, 2).Value = Cells(4, 13).Value
k = Application.match(Cells(i, 12), Worksheets("sheet2").Rows(1), 0)
n = Worksheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("sheet2").Cells(n + 1, 1).Value = Cells(5, 13).Value
Worksheets("sheet2").Cells(n + 1, k).Value = Cells(i, 13).Value
Worksheets("sheet2").Cells(n + 1, 2).Value = Cells(4, 13).Value
Else
End If

Next i

End Sub

In the attached file you can click the button in cell L2 to run the macro. The history of all prices is in sheet2. The summary table is in sheet1 and the input range is M4:M13 in sheet1. The number of types and apple orchards can be adapted as required.

[#3]

@OliverScheurich 

Thanks a lot! This is really helpful. Is there a way to make the inputs and the button on a different sheet than the outsputs so that it isn't all on the same one? Wasn't sure how to do that. Either way I appreciate it!

[#4]

@neurtron1080 

You are welcome. In the attached file there are seperate sheets for input, summary and history.