Help with a "simple" autofill macro

[Guitarmageddon]

Hello everyone. Needed some help with what seems to me would be a simple autofill macro. Below is a report output for one store. As you can see, it has some of the cells merged together, until the next new values occur in column A, C, D. I have several other sheets in the workbook that output for different stores, each store being on its own tab. I would like to:

A- unmerge the columns A,C,D and basically "Repeat item labels" as you would in a pivot table. I.e. copy the values down until the next new value occurs. For example, copy site ID 04100 down to row 10, in category, copy cosmetics down to row 5, copy hair care down to row 7 etc Im sure you know what I mean.

and then

B- move to the next tab in the workbook and do the same until all tabs are gone through, although that tabs range and list of categories etc will be dynamic. All the sheets start like the example below though, with colum headers being in row 3 and then the data following the same layout from there.

Ideally I would just modify the source report to repeat these labels for me but that is proving to be difficult for me to do right now. Any help would be greatly appreciated.

 

[Guitarmageddon]

So I took a pause and I think that power query is the way to go for my end result, so I posted there and would like to shift focus to that method.

whats the best way to copy query steps do dozens of sheets/queries?

So here is the gist of how my source data workbook looks, its kinda messy: I want to use power query to basically fill down the cells (originally I was going to try and use VBA but I saw power query has a function for this!), and then combine the data from all the sheets. I get as far as...

www.mrexcel.com