Macro that subtotals and formats refreshed Power Query data.

jamiemarie · Jul 7, 2020

Hi all. Before I begin, I'd just like to say thanks for all the help I've received in the past couple weeks, ya'll are amazing for volunteering to help so many people!

Here's my problem:
I have a fabric inventory table that I refresh through Power Query. I then change the table to a range to subtotal and format so taking inventory is easier. I recorded a macro to help with this, I will leave the code below. The macro subtotals the Bin Location Column and puts the count in the Batch Column, then the formatting starts. It turns all the Count rows dark green, makes the Batch column bold, then finds and replaces the word Count with a space so there is no word there.
The problem comes when I refresh the table, if I have more Bins than when I originally recorded the macro, It won't change the new Bin Locations to the dark green color and doesn't bold the count in the batch column. Can someone please help with my macro to have it do this? Information below.



Bin Location	Batch	AX Count	SE2 Count	Item Id	Item Name
MS13001B	482392	2		F-1615	PERFORMANCE YARN DYED LINEN WEAVE, INDIGO
MS13001B	482395	4.24		F-1615	PERFORMANCE YARN DYED LINEN WEAVE, INDIGO
MS13001B	482643	15		F-1615	PERFORMANCE YARN DYED LINEN WEAVE, INDIGO
MS13001B	596351	5		F-1616	PERFORMANCE YARN DYED LINEN WEAVE, SHELTER BLUE
MS13001B	620544	45		F-1619	PERFORMANCE TWILL, GRANITE
MS13001B	620264	20		F-1621	PERFORMANCE TWILL, TEAL
MS13001B	614062	3		F-1812	PERFORMANCE YARN DYED LINEN WEAVE, FRENCH BLUE
MS13001C	601347	2		F-1620	PERFORMANCE TWILL, STONE
MS13001C	577190	5.99		F-1622	PERFORMANCE BASKET SLUB, DARK HORSERADISH
MS13001C	559761	2.15		F-1624	PERFORMANCE BASKET SLUB, MIDNIGHT
MS13001C	626841	3.13		F-1698	PERFORMANCE BASKET SLUB, PEWTER GREY
MS13001C	628884	3.93		F-1732	PERFORMANCE COASTAL LINEN, PEBBLE STONE
MS13001C	584784	45		F-1791	PERFORMANCE COASTAL LINEN, OATMEAL
MS13001C	585909	3		F-1791	PERFORMANCE COASTAL LINEN, OATMEAL
MS13001D	597772	6.28		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MS13001D	415538	3		F-0979	PERFORMANCE BASKETWEAVE, METAL
MS13001D	603477	12.9		F-0979	PERFORMANCE BASKETWEAVE, METAL
MS13001D	627251	3		F-1614	PERFORMANCE YARN DYED LINEN WEAVE, FROST GREY
MS13001D	627256	53.4		F-1614	PERFORMANCE YARN DYED LINEN WEAVE, FROST GREY
MS13001D	496076	2		F-1625	PERFORMANCE EVERYDAY VELVET, INK BLUE
MS13001D	504439	2		F-1625	PERFORMANCE EVERYDAY VELVET, INK BLUE
MS13002A	634409	2		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MS13002A	634410	56.1		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MS13002A	634411	56		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MS13002A	634412	61.1		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MS13002A	634413	30		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MS13002B	376076	3		F-0873	YARN DYED LINEN WEAVE, INDIGO
MS13002B	634389	16.88		F-1027	YARN DYED LINEN WEAVE, STONE WHITE
MS13002B	582927	20		F-1795	YARN DYED LINEN WEAVE, STEEL GREY
MS13002B	587710	0.69		F-1796	YARN DYED LINEN WEAVE, NATURAL
MS13002C	358733	30		F-0426	LINEN WEAVE, NATURAL
MS13002C	324577	10		F-0920	BASKETWEAVE SLUB, ASH
MS13002D	602191	1		F-0425	PEBBLE WEAVE, OATMEAL
MS13002D	628073	3.31		F-0477	PEBBLE WEAVE, AEGEAN BLUE
MS13002D	321897	15		F-0905	WORN VELVET, METAL
MS13003B	627986	1		F-0989	BASKET SLUB, FEATHER GREY
MS13003B	601813	2		F-1114	BASKET SLUB, DARK HORSERADISH
MS13003B	582954	1		F-1592	BASKET SLUB, BLUE STONE
MS13003B	590227	3.21		F-1594	BASKET SLUB, DESERT SUNSET
MS13003C	617505	3		F-0398	MARLED MICROFIBER, HEATHER GREY
MS13003C	562554	8		F-1598	CHUNKY BASKETWEAVE, CHARCOAL
MS13003C	565009	8		F-1599	CHUNKY BASKETWEAVE, METAL
MS13003C	590202	30.68		F-1599	CHUNKY BASKETWEAVE, METAL
MS13003C	516986	3		F-1600	CHUNKY BASKETWEAVE, SLATE
MS13003D	33192	4		F-0398	MARLED MICROFIBER, HEATHER GREY
MS13003D	618020	8		F-0398	MARLED MICROFIBER, HEATHER GREY
MS13003D	134652	58.53		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	134778	53.61		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	134779	53.61		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	134780	57.99		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	134781	1.99		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	134782	6.26		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	134783	18		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	139584	2		F-0621	MARLED MICROFIBER, ASH GREY
MS13003D	614193	45.95		F-0621	MARLED MICROFIBER, ASH GREY
MS13004C	639480	45.95		F-0227	PERF VELVET, DOVE GREY
MS13004C	637511	13.57		F-0359	PERF VELVET, STONE
MS13004C	641659	20.79		F-0359	PERF VELVET, STONE
MS13004C	325541	2		F-0445	PERF VELVET, MOSS
MS13004C	639581	12.54		F-0445	PERF VELVET, MOSS
MS13004C	639589	47.05		F-0445	PERF VELVET, MOSS
MS13004C	639590	12.26		F-0445	PERF VELVET, MOSS
MS13004D	638044	5		F-0227	PERF VELVET, DOVE GREY
MS13004D	639481	3.64		F-0227	PERF VELVET, DOVE GREY
MS13005B	23728	38		F-0357	PERF VELVET, LAGOON
MS13005B	590258	3.84		F-0357	PERF VELVET, LAGOON
MS13005B	639375	10		F-0357	PERF VELVET, LAGOON
MS13005B	639416	22.02		F-0357	PERF VELVET, LAGOON
MS13005B	639421	12		F-0357	PERF VELVET, LAGOON
MS13005D	602475	2.94		F-0230	PERF VELVET, OTTER
MS13006B	390079	1		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MS13006B	637631	30		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MS13006C	601732	5		F-1060	DISTRESSED VELVET, PEACOCK
MS13006C	397724	0.77		F-1062	DISTRESSED VELVET, FOREST
MS13006C	598882	2		F-1063	DISTRESSED VELVET, OLIVE
MS13006C	610552	4		F-1063	DISTRESSED VELVET, OLIVE
MS13006C	610555	8		F-1063	DISTRESSED VELVET, OLIVE
MS13006C	614085	1.63		F-1113	DISTRESSED VELVET, RUST
MS13006D	397486	1		F-1059	DISTRESSED VELVET, MINERAL GREY
MS13006D	635167	2.14		F-1059	DISTRESSED VELVET, MINERAL GREY
MS13006D	639492	7.5		F-1059	DISTRESSED VELVET, MINERAL GREY
MS13006D	639494	30		F-1059	DISTRESSED VELVET, MINERAL GREY
MS13007B	135406	44.5		F-0675	CHENILLE TWEED, FROST GRAY
MS13007B	135407	44.4		F-0675	CHENILLE TWEED, FROST GRAY
MS13007B	394765	5		F-0675	CHENILLE TWEED, FROST GRAY
MS13007B	617363	43.5		F-0675	CHENILLE TWEED, FROST GRAY
MS13007B	617370	2.15		F-0675	CHENILLE TWEED, FROST GRAY
MS13007B	579142	3		F-0676	CHENILLE TWEED, NIGHTSHADE
MS13007B	611442	2.55		F-0676	CHENILLE TWEED, NIGHTSHADE
MS13007B	625002	28.32		F-0676	CHENILLE TWEED, NIGHTSHADE
MS13007B	613552	12		F-1693	CHENILLE TWEED, IRONGATE
MS13007B	634397	7.2		F-1693	CHENILLE TWEED, IRONGATE
MS13007B	634405	16		F-1693	CHENILLE TWEED, IRONGATE
MS13007B	634406	9.86		F-1693	CHENILLE TWEED, IRONGATE
MS13007C	617220	4.75		F-0673	CHENILLE TWEED, SLATE
MS13007C	636369	12.08		F-0673	CHENILLE TWEED, SLATE
MS13007C	285746	10		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007C	584380	8		F-1699	CHENILLE TWEED,PEWTER
MS13007C	602917	43.5		F-1699	CHENILLE TWEED,PEWTER
MS13007C	607540	3		F-1699	CHENILLE TWEED,PEWTER
MS13007C	607543	19.73		F-1699	CHENILLE TWEED,PEWTER
MS13007D	135442	46.2		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135443	44.5		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135453	43.1		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135454	44.1		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135455	42.9		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135457	44.7		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135459	44.5		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135460	44.7		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	135462	44.6		F-0674	CHENILLE TWEED, FEATHER GREY
MS13007D	400348	3.37		F-0674	CHENILLE TWEED, FEATHER GREY
MS13008A	247790	47.6		F-0821	WE-TWILL, STONE
MS13008B	247791	3		F-0821	WE-TWILL, STONE
MS13008B	245285	52.2		F-0822	WE-TWILL, GRAVEL
MS13008B	365875	1		F-0822	WE-TWILL, GRAVEL
MS13008B	608533	37.29		F-0822	WE-TWILL, GRAVEL
MS13008B	639607	3.71		F-0897	WE-TWILL, WHEAT
MS13008B	639608	12.54		F-0897	WE-TWILL, WHEAT
MS13008B	616922	12		F-0996	WE-TWILL, TEAL
MS13008B	627929	27.48		F-1024	WE-TWILL, PLATINUM
MS13008B	580798	40		F-1025	WE-TWILL, BLACK INDIGO
MS13008B	618359	34		F-1656	WE-TWILL, MINT
MS13008B	618472	12		F-1656	WE-TWILL, MINT
MS13008C	443967	15		F-0929	ECO WEAVE, PEWTER
MS13008D	590067	4.51		F-1655	CHENILLE TWEED, ROSETTE
MS13008D	590068	34.54		F-1655	CHENILLE TWEED, ROSETTE
MS13009B	560432	43.74		V-0002	VEGAN LEATHER, SNOW
MS13009C	289635	10		F-0299	TXTD BASKETWEAVE, FLAX
MS13009C	613996	5		F-0700	HEATHERED WEAVE, EUCALYPTUS
MS13009C	587105	25		F-0701	HEATHERED WEAVE, CAYENNE
MS13009C	281957	15		F-0755	HEATHERED CROSSHATCH, FEATHER GREY
MS13009C	281962	10		F-0755	HEATHERED CROSSHATCH, FEATHER GREY
MS13009C	524110	40.3		F-0845	PERF CROSS WEAVE, PEBBLE
MS13009C	327602	15		F-0943	PERF HEATHERED TWEED, IVORY
MS13009C	613393	11.9		F-1028	HEATHERED CROSSHATCH, NATURAL
MS13009D	613850	15.98		F-0700	HEATHERED WEAVE, EUCALYPTUS
MS13009D	327738	20		F-0785	HEATHERED TWEED, CHARCOAL
MS13009D	629636	12.6		F-0785	HEATHERED TWEED, CHARCOAL
MS13009D	586826	22.54		F-0819	HEATHERED TWEED, MARINE
MS13009D	586845	6.97		F-0819	HEATHERED TWEED, MARINE
MS13009D	452261	20		F-1067	HEATHERED CROSSHATCH, SLATE
MS13010B	634346	2		F-1112	WASHED CANVAS, FEATHER GREY
MS13010B	634360	18		F-1112	WASHED CANVAS, FEATHER GREY
MS13010B	639435	11.83		F-1112	WASHED CANVAS, FEATHER GREY
MS13010B	639437	14.5		F-1112	WASHED CANVAS, FEATHER GREY
MS13010B	590006	20		F-1701	LUXE BOUCLE,ANGORA BEIGE
MS13010C	524172	10		F-1652	DECO WEAVE, STONE
MS13010C	615908	6		F-1809	DRAWN DOTS, INDIGO
MS13010D	455784	6		F-0792	LUSTRE VELVET, DUSTY BLUSH
MS13010D	494679	1		F-0792	LUSTRE VELVET, DUSTY BLUSH
MS13010D	532523	8		F-0792	LUSTRE VELVET, DUSTY BLUSH
MS13010D	586986	20		F-0793	LUSTRE VELVET, CELESTIAL BLUE
MS13010D	517137	1		F-0890	PLUSH VELVET, WASABI
MS13010D	580827	5		F-0890	PLUSH VELVET, WASABI
MS13010D	608192	6		F-0890	PLUSH VELVET, WASABI
MS13010D	442006	3		F-1139	TEXTURED TWILL, KHAKI
MS13010D	442007	1		F-1139	TEXTURED TWILL, KHAKI
MS13010D	524974	3		F-1659	CANNES, SILVERSAGE
MS13010D	626503	26.74		F-1703	LUXE BOUCLE,STONE WHITE
MS13011B	425083	2		F-1073	ASTOR VELVET, EVERGREEN
MS13011B	446047	8		F-1074	ASTOR VELVET, DUSTY BLUSH
MS13011B	587317	35.3		F-1075	ASTOR VELVET, FROST GREY
MS13011B	446040	30		F-1079	ASTOR VELVET, STONE
MS13011B	485327	37		F-1079	ASTOR VELVET, STONE
MS13011B	616269	5		F-1793	ASTOR VELVET, PINK GRAPEFRUIT
MS13011C	175261	30.68		F-0739	TWEED, ASH
MS13011C	175283	45.1		F-0739	TWEED, ASH
MS13011C	175284	44		F-0739	TWEED, ASH
MS13011C	175285	42.8		F-0739	TWEED, ASH
MS13011C	175286	43		F-0739	TWEED, ASH
MS13011C	175287	42.2		F-0739	TWEED, ASH
MS13011C	175288	43.3		F-0739	TWEED, ASH
MS13011C	175290	39.4		F-0739	TWEED, ASH
MS13011C	175291	37.8		F-0739	TWEED, ASH
MS13011C	191015	41.5		F-0739	TWEED, ASH
MS13011C	202351	40.6		F-0739	TWEED, ASH
MS13011C	493802	12		F-0739	TWEED, ASH
MS13011C	523867	3		F-0739	TWEED, ASH
MS13011D	585463	2		F-1074	ASTOR VELVET, DUSTY BLUSH
MS13011D	587221	1		F-1074	ASTOR VELVET, DUSTY BLUSH
MS13011D	456438	10		F-1075	ASTOR VELVET, FROST GREY
MS13011D	587587	3		F-1076	ASTOR VELVET, IRON
MS13011D	639605	11.89		F-1078	ASTOR VELVET, STEEL BLUE
MS13011D	575247	15		F-1080	ASTOR VELVET, NICKEL
MS13011D	575386	30		F-1080	ASTOR VELVET, NICKEL
MS13011D	435400	2		F-1082	ASTOR VELVET, SLATE
MS13011D	435406	2		F-1082	ASTOR VELVET, SLATE
MS13011D	572031	15.65		F-1082	ASTOR VELVET, SLATE
MSFLOOR1	633957	525.16		F-0227	PERF VELVET, DOVE GREY
MSFLOOR1	634009	530.64		F-0227	PERF VELVET, DOVE GREY
MSFLOOR1	639479	51.42		F-0227	PERF VELVET, DOVE GREY
MSFLOOR1	633547	425.59		F-0357	PERF VELVET, LAGOON
MSFLOOR1	639411	33.37		F-0357	PERF VELVET, LAGOON
MSFLOOR1	639415	42.12		F-0357	PERF VELVET, LAGOON
MSFLOOR1	639417	45.95		F-0357	PERF VELVET, LAGOON
MSFLOOR1	639419	54.7		F-0357	PERF VELVET, LAGOON
MSFLOOR1	639420	47.05		F-0357	PERF VELVET, LAGOON
MSFLOOR1	634822	345.19		F-0359	PERF VELVET, STONE
MSFLOOR1	641869	48.7		F-0821	WE-TWILL, STONE
MSFLOOR1	641871	45.4		F-0821	WE-TWILL, STONE
MSFLOOR1	641873	46.7		F-0821	WE-TWILL, STONE
MSFLOOR1	641874	48.7		F-0821	WE-TWILL, STONE
MSFLOOR1	641876	47		F-0821	WE-TWILL, STONE
MSFLOOR1	641878	44.9		F-0821	WE-TWILL, STONE
MSFLOOR1	641879	45.6		F-0821	WE-TWILL, STONE
MSFLOOR1	641880	46.9		F-0821	WE-TWILL, STONE
MSFLOOR1	641882	47.3		F-0821	WE-TWILL, STONE
MSFLOOR1	641884	48.7		F-0821	WE-TWILL, STONE
MSFLOOR1	641885	46.9		F-0821	WE-TWILL, STONE
MSFLOOR1	641886	44.7		F-0821	WE-TWILL, STONE
MSFLOOR1	641887	48.1		F-0821	WE-TWILL, STONE
MSFLOOR1	641888	46.8		F-0821	WE-TWILL, STONE
MSFLOOR1	641889	50.8		F-0821	WE-TWILL, STONE
MSFLOOR1	641890	46.2		F-0821	WE-TWILL, STONE
MSFLOOR1	641813	56		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	641948	58		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	641951	57		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	641952	58		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	641954	57		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	641955	57		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	641957	58		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	641958	57		F-0928	ECO WEAVE, OYSTER
MSFLOOR1	578028	503.5		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MSFLOOR1	622126	414.9		F-1027	YARN DYED LINEN WEAVE, STONE WHITE
MSFLOOR1	579165	428.1		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	579850	425		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637627	51.7		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637628	47.1		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637632	48.6		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637633	46.8		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637635	51.4		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637636	45.9		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637637	47.7		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637638	51.6		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637639	50		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637640	46.7		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637641	46.4		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637642	46.4		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637644	48		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637645	49.8		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	637652	37.6		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MSFLOOR1	530739	429.2		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	531016	427.2		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	535260	396		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	639488	50.2		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	639489	56.9		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	639491	45.9		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	639493	55.1		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	639496	44.2		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	639498	43.5		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641841	53.6		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641845	49.8		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641852	47.4		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641853	43.5		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641855	49.5		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641856	48.2		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641858	50.3		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641860	51.7		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	641861	48.8		F-1059	DISTRESSED VELVET, MINERAL GREY
MSFLOOR1	639429	26.8		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639431	28.2		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639432	27		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639433	27.7		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639434	21.6		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639441	25.7		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639442	25.7		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639455	28.3		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639457	25.3		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639458	31		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639459	24.5		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	639460	27		F-1112	WASHED CANVAS, FEATHER GREY
MSFLOOR1	634171	521.7		F-1693	CHENILLE TWEED, IRONGATE
MSFLOOR1	634401	43.5		F-1693	CHENILLE TWEED, IRONGATE
MSFLOOR1	593446	393.66		V-0001	VEGAN LEATHER,NUT
MSFLOOR1	626619	610.52		V-0001	VEGAN LEATHER,NUT
MSFLOOR1	633466	43.74		V-0003	VEGAN LEATHER, CINDER
MSFLOOR1	639615	43.74		V-0003	VEGAN LEATHER, CINDER
MSFLOOR1	601783	31.71		V-0004	VEGAN LEATHER, MOLASSES
MSFLOOR2	640288	59.08		F-0443	PERF VELVET, INK BLUE
MSFLOOR2	640290	55.8		F-0443	PERF VELVET, INK BLUE
MSFLOOR2	640291	56.35		F-0443	PERF VELVET, INK BLUE
MSFLOOR2	640292	55.25		F-0443	PERF VELVET, INK BLUE
MSFLOOR2	640293	54.16		F-0443	PERF VELVET, INK BLUE
MSFLOOR2	640294	46.5		F-0443	PERF VELVET, INK BLUE
MST003	639482	25		F-0227	PERF VELVET, DOVE GREY
MST003	637514	15.32		F-0359	PERF VELVET, STONE
MST003	59500	10		F-0447	PERF VELVET, SHADOW
MST004	639414	29.54		F-0357	PERF VELVET, LAGOON
MST004	637630	44.8		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MST004	639490	45.4		F-1059	DISTRESSED VELVET, MINERAL GREY
MST004	639436	28.7		F-1112	WASHED CANVAS, FEATHER GREY
MST004	639440	28.4		F-1112	WASHED CANVAS, FEATHER GREY
MST006	637505	4		F-0359	PERF VELVET, STONE
MST006	637512	44.86		F-0359	PERF VELVET, STONE
MST007	59501	40		F-0447	PERF VELVET, SHADOW
MST007	613382	3		F-0840	DECO WEAVE, FEATHER GREY
MST007	641322	55		F-0928	ECO WEAVE, OYSTER
MST007	641323	35		F-0928	ECO WEAVE, OYSTER
MST007	641325	44		F-0928	ECO WEAVE, OYSTER
MST007	614001	30		F-0978	PERFORMANCE BASKETWEAVE, NATURAL
MST007	637629	43.1		F-1034	DISTRESSED VELVET, LIGHT TAUPE
MST007	607483	53.9		F-1622	PERFORMANCE BASKET SLUB, DARK HORSERADISH
MST007	634404	43.2		F-1693	CHENILLE TWEED, IRONGATE

Macro:

VBA Code:

Sub Subtotal_Format()
'
' Subtotal_Format Macro
' Subtotal Bin Loc Count in Batch Column and Format
'

'
    Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=False
    Columns("A:F").Select
    Selection.AutoFilter
    Range("A3").Select
    ActiveSheet.Range("$A$1:$F$345").AutoFilter Field:=1, Criteria1:=Array( _
        "Grand Count", "MS13001B Count", "MS13001C Count", "MS13001D Count", _
        "MS13002A Count", "MS13002B Count", "MS13002C Count", "MS13002D Count", _
        "MS13003B Count", "MS13003C Count", "MS13003D Count", "MS13004C Count", _
        "MS13004D Count", "MS13005B Count", "MS13005D Count", "MS13006B Count", _
        "MS13006C Count", "MS13006D Count", "MS13007B Count", "MS13007C Count", _
        "MS13007D Count", "MS13008A Count", "MS13008B Count", "MS13008C Count", _
        "MS13008D Count", "MS13009B Count", "MS13009C Count", "MS13009D Count", _
        "MS13010B Count", "MS13010C Count", "MS13010D Count", "MS13011B Count", _
        "MS13011C Count", "MS13011D Count", "MSFLOOR1 Count", "MSFLOOR2 Count", _
        "MST003 Count", "MST004 Count", "MST006 Count", "MST007 Count"), Operator:= _
        xlFilterValues
    Range("A2:F335").Select
    With Selection.Interior
        .PatternThemeColor = xlThemeColorAccent6
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0.799981688894314
    End With
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    ActiveSheet.Range("$A$1:$F$345").AutoFilter Field:=1
    Columns("A:A").Select
    Selection.Replace What:="Count", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A3").Select
End Sub

jamiemarie · Jul 7, 2020

Oh, Also, here is a screenshot showing the new Bin not being formatted. The find and replace is the only part that worked. Thank again to anyone who can help.

jamiemarie · Jul 8, 2020

Maybe the solution is to use a formula instead of subtotaling the data. I have been googling for the past three days, and while I have found lots of posts, I can't find this exact problem. It doesn't seem to be the known problem with conditional formatting and it's not a pivot table. It also doesn't seem to be a problem with my range in my macro. I'm just at a loss. I have the freedom to make the sheet however I want, so I don't have to do anything a certain way as long as the result is the same. I also need to be able to format quickly, which is why I used a macro in the first place. We are told when to refresh our main sheet and we have to count as soon as possible since the plant is still in production while we count.

Any ideas will be welcome!

RoryA · Jul 8, 2020

Your code refers to a specific range so if your query returns new data, it won't get included. I suggest you use Range("A1").Currentregion instead of specifying the number of rows.

jamiemarie · Jul 8, 2020

RoryA said:
Your code refers to a specific range so if your query returns new data, it won't get included. I suggest you use Range("A1").Currentregion instead of specifying the number of rows.

Thank you so much for taking the time to help! I changed the range to your suggestion and it didn't work, so I changed a few more and it still isn't working. Could you please look at the new code and tell me where I should be changing it?

VBA Code:

Sub Subtotal_Format()
'
' Subtotal_Format Macro
' Subtotal Bin Loc Count in Batch Column and Format
'

'
    Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(2), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=False
    Columns("A:F").Select
    Selection.AutoFilter
    Range("A3").Select
    Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=Array( _
        "Grand Count", "MS13001B Count", "MS13001C Count", "MS13001D Count", _
        "MS13002A Count", "MS13002B Count", "MS13002C Count", "MS13002D Count", _
        "MS13003B Count", "MS13003C Count", "MS13003D Count", "MS13004C Count", _
        "MS13004D Count", "MS13005B Count", "MS13005D Count", "MS13006B Count", _
        "MS13006C Count", "MS13006D Count", "MS13007B Count", "MS13007C Count", _
        "MS13007D Count", "MS13008A Count", "MS13008B Count", "MS13008C Count", _
        "MS13008D Count", "MS13009B Count", "MS13009C Count", "MS13009D Count", _
        "MS13010B Count", "MS13010C Count", "MS13010D Count", "MS13011B Count", _
        "MS13011C Count", "MS13011D Count", "MSFLOOR1 Count", "MSFLOOR2 Count", _
        "MST003 Count", "MST004 Count", "MST006 Count", "MST007 Count"), Operator:= _
        xlFilterValues
    Range("A1").CurrentRegion.Select
    With Selection.Interior
        .PatternThemeColor = xlThemeColorAccent6
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0.799981688894314
    End With
    Selection.Font.Bold = False
    Selection.Font.Bold = True
    Range("A1").CurrentRegion.AutoFilter Field:=1
    Columns("A:A").Select
    Selection.Replace What:="Count", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("A3").Select
End Sub

RoryA · Jul 8, 2020

What exactly does "didn't work" actually mean? What happened?

jamiemarie · Jul 8, 2020

RoryA said:
What exactly does "didn't work" actually mean? What happened?

Oh sorry, It still didn't format the new data. (Making it bold and coloring the rows dark green.) So MST008 in post #2 still looks the same instead of being formatted like the others.

RoryA · Jul 8, 2020

I suspect your autofilter is the issue as it is filtering for a specific list. Try amending it from this:

Code:

ActiveSheet.Range("$A$1:$F$345").AutoFilter Field:=1, Criteria1:=Array( _
"Grand Count", "MS13001B Count", "MS13001C Count", "MS13001D Count", _
"MS13002A Count", "MS13002B Count", "MS13002C Count", "MS13002D Count", _
"MS13003B Count", "MS13003C Count", "MS13003D Count", "MS13004C Count", _
"MS13004D Count", "MS13005B Count", "MS13005D Count", "MS13006B Count", _
"MS13006C Count", "MS13006D Count", "MS13007B Count", "MS13007C Count", _
"MS13007D Count", "MS13008A Count", "MS13008B Count", "MS13008C Count", _
"MS13008D Count", "MS13009B Count", "MS13009C Count", "MS13009D Count", _
"MS13010B Count", "MS13010C Count", "MS13010D Count", "MS13011B Count", _
"MS13011C Count", "MS13011D Count", "MSFLOOR1 Count", "MSFLOOR2 Count", _
"MST003 Count", "MST004 Count", "MST006 Count", "MST007 Count"), Operator:= _
        xlFilterValues

to this:

Code:

Activesheet.range("A1").currentregion.autofilter field:=1, criteria1:="*Count"

jamiemarie · Jul 8, 2020

RoryA said:

I suspect your autofilter is the issue as it is filtering for a specific list. Try amending it from this:

Code:

ActiveSheet.Range("$A$1:$F$345").AutoFilter Field:=1, Criteria1:=Array( _
"Grand Count", "MS13001B Count", "MS13001C Count", "MS13001D Count", _
"MS13002A Count", "MS13002B Count", "MS13002C Count", "MS13002D Count", _
"MS13003B Count", "MS13003C Count", "MS13003D Count", "MS13004C Count", _
"MS13004D Count", "MS13005B Count", "MS13005D Count", "MS13006B Count", _
"MS13006C Count", "MS13006D Count", "MS13007B Count", "MS13007C Count", _
"MS13007D Count", "MS13008A Count", "MS13008B Count", "MS13008C Count", _
"MS13008D Count", "MS13009B Count", "MS13009C Count", "MS13009D Count", _
"MS13010B Count", "MS13010C Count", "MS13010D Count", "MS13011B Count", _
"MS13011C Count", "MS13011D Count", "MSFLOOR1 Count", "MSFLOOR2 Count", _
"MST003 Count", "MST004 Count", "MST006 Count", "MST007 Count"), Operator:= _
        xlFilterValues

to this:

Code:

Activesheet.range("A1").currentregion.autofilter field:=1, criteria1:="*Count"

That did it! Thank you so much!

RoryA · Jul 8, 2020

Glad to help.

Macro that subtotals and formats refreshed Power Query data.

jamiemarie

Board Regular

Excel Facts

jamiemarie

Board Regular

Attachments

jamiemarie

Board Regular

RoryA

MrExcel MVP, Moderator

jamiemarie

Board Regular

RoryA

MrExcel MVP, Moderator

jamiemarie

Board Regular

RoryA

MrExcel MVP, Moderator

jamiemarie

Board Regular

RoryA

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Macro that subtotals and formats refreshed Power Query data.

Board Regular

Excel Facts

Board Regular

Attachments

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock