VBA for making dynamic bucketwise report from Ageing report while providing days from Form

[ravi123]

Hello All,

I'm developing a dynamic bucket wise OS report maker from Ageing report wherein users enter the days through form and by clicking the button "Make Bucketwise", the Bucketwise OS report will be generated (adjacent to the Ageing report) as per the days provided by users. Here users may enter any day i.e say one may want to make report for bucket 0-21 days however another user may want to generate for 0-16,17-21 and so on. The number of columns in the ageing report is fixed (and for ageing it is from Day 0 to Day 120 Plus) however number of rows may vary. File attached.

I had written codes for few ageing combinations however I'm not sure how I would write the code for all possible different combinations that ranges from Day 0 (Col.Q) to Day 120 Plus (Col.EH)? Also I'm now getting compile error procedure too large as my code is getting too long.

I'm sure there may be another way round wherein with the help of some declarations all combinations could be achieved.

Any help or suggestions will be highly appreciated and sorry for the long description.

DynamicBucketWise.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER
1	S. No.	Dealer Id	Dealer Name	Region	Sub Region	Unit Name	Sales Manager	Unit Head	Original Credit Limit	Temporary Credit Limit	Total Credit Limit	Available Credit Limit	Security Deposit	Uncleared Cheque Amount	Total Outstanding	Outstanding value in SAP	Day 0	Day 1	Day 2	Day 3	Day 4	Day 5	Day 6	Day 7	Day 8	Day 9	Day 10	Day 11	Day 12	Day 13	Day 14	Day 15	Day 16	Day 17	Day 18	Day 19	Day 20	Day 21	Day 22	Day 23	Day 24	Day 25	Day 26	Day 27	Day 28	Day 29	Day 30	Day 31	Day 32	Day 33	Day 34	Day 35	Day 36	Day 37	Day 38	Day 39	Day 40	Day 41	Day 42	Day 43	Day 44	Day 45	Day 46	Day 47	Day 48	Day 49	Day 50	Day 51	Day 52	Day 53	Day 54	Day 55	Day 56	Day 57	Day 58	Day 59	Day 60	Day 61	Day 62	Day 63	Day 64	Day 65	Day 66	Day 67	Day 68	Day 69	Day 70	Day 71	Day 72	Day 73	Day 74	Day 75	Day 76	Day 77	Day 78	Day 79	Day 80	Day 81	Day 82	Day 83	Day 84	Day 85	Day 86	Day 87	Day 88	Day 89	Day 90	Day 91	Day 92	Day 93	Day 94	Day 95	Day 96	Day 97	Day 98	Day 99	Day 100	Day 101	Day 102	Day 103	Day 104	Day 105	Day 106	Day 107	Day 108	Day 109	Day 110	Day 111	Day 112	Day 113	Day 114	Day 115	Day 116	Day 117	Day 118	Day 119	Day 120	Day 120 Plus	Previous Month Invoices CD Diferential	Current Month Invoices CD Diferential	Invoice Amount Pending For Monthend Knock Off	Latest Invoice Date	Lastest Invoice Number	Latest Payment Date	Posting Date	Baseline Date	Unsecured Amount	Zone
2	1	123123	Sample Dealer 1	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	278409.75	0	0	249660	0	0	0	3456	0	0	0	0	0	0	23234	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2059.75	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data
3	2	121212	Sample Dealer 2	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	12345	0	0	0	0	0	0	0	0	0	0	0	12345	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data
4	3	131313	Sample Dealer 3	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	317599.79	0	0	0	0	3435	0	0	0	0	0	0	0	0	0	0	2323	0	0	0	0	0	0	0	0	0	0	0	0	0	5853.51	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	235000	0	70988.28	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data
5	4	111222	Sample Dealer 4	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	226924.06	12312	0	0	0	0	2345	0	0	0	0	207700.06	0	0	0	0	0	0	0	4567	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data	Dummy Data
Ageing Report