I have an excel file with different sheets for every week of the year, each named "Week 1", "Week 2" etc.
Each sheet lists the daily sales of retail locations, each divided by department, as shown in the linked image.
A example sheet showing the first 4 locations in Week 14
I want to use VBA to compile all the rows pertaining to a particular location across all the weeks in the workbook. I want to create a Summary sheet with sales for "Johnstown" from Weeks 1 through 52
However, a static range reference doesn't work because the locations don't have the same position in each sheet, e.g. "Jamestown" might appear first in Week 14, but be further down the sheet another week.
Similarly, new departments appear for a few weeks then disappear, e.g. Christmas Products, so sometimes a location's infor is 6 row long sometimes 7 rows.
I have already tried to adapt the approaches I've seen in other similar questions here and elsewhere, but I don't have enough VBA experience to really understand exactly how and I haven't gotten things to work as I need them yet.
I'd really appreciate some help with this. Thanks!