Aug 09 2022 10:06 AM
Aug 09 2022 10:06 AM
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!
Aug 09 2022 12:11 PMSolution
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.
Aug 09 2022 02:25 PM
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!
Aug 10 2022 01:17 AM
You are welcome. In the attached file there are seperate sheets for input, summary and history.