Help with a "simple" autofill macro

Guitarmageddon

Board Regular
Joined
Dec 22, 2014
Messages
92
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.

1586263722136.png
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Guitarmageddon

Board Regular
Joined
Dec 22, 2014
Messages
92
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,798
Members
410,708
Latest member
SanTrapGamer
Top