Create Workbooks and Worksheets with specific formats

[Blaster1]

Hello All,

Need your help with a VBA Code please.
See below tables and requirement of code to do the following:

1) Check table1 and create 6 workbooks.
a. Workbook 1 named AB-01 and has two worksheets (AB-01108 and AB-01115)
b. Workbook 2 named AB-02 and has four worksheets (AB-02112, AB-02113, AB-02114 and AB-02115)
c. Workbook 3 named AB-04 and has two worksheets (AB-04106 and AB-04110)
d. Workbook 4 named AB-05 and has two worksheets (AB-05113 and AB-05157)
e. Workbook 5 named AB-06 and has two worksheets (AB-06117 and AB-06119)
f. Workbook 6 named AB-16 and has one worksheet (AB-16120)
Note, Original file have hundreds of entries so logic will be to look at each name in columns A, and then create a new work book whenever I have "AB-XX"; XX is a new number like 01, 02, 14, 16 etc... if the number is repeated many times, then those will be worksheets in initial workbook ... i.e. if I have 6 rows all starting with AB-01 then one workbook with 6 worksheets inside workbook 01.


2) Each worksheet should be of the same format of table 2 below.
a. In table 2, Column A rows should have the same exact names as Column A of table 1.
b. In table 2, Columns C, D, E and F should have fixed text.
c. In table 2, Column G should have the units of 1st row in table 1.
d. In table 2, Column H should have the figures extracted from table 1 per unit.
e. In table 2, Column I should be equal to Column H in table 2.
f. In table 2, Columns M, N & O should have respectivley: "John Smith", "Date", "John Smith" with Mistral Font.
g. In table 2, select whole table from A7 to P7 and replace all blank cells with "Text".
h. In table 2, Field E2 should have same name of all TEXT after AB-XXXXX.
i. In table 2, Field C2 should have all number after it's AB-, ie for AB-01108 ABC-XYZ-FFF-01, C2 should be 01108
j. In table 2, Field B2 should be last two numbers of C2 i.e. 08 in above example.
k. In table 2, Field E3 should be fixed "Text".
l. In table 2, lock all columns to specific ColumnWidth per each columns (I will insert the value on the code).
m. Save all and quit.

TABLE 1

Name		Unit 1	Unit 2	Unit 3	Unit A	Unit B	Unit C
AB-01108 ABC-XYZ-FFF-01		14.0 ABC	8.3 DEF	0.6 GHI	0.0	56.0	24.9
AB-01115 ABC-XYZ-FFF-02		38.0 ABC	24.3 DEF	9.2 GHI	0.0	---	74.6
AB-02112 ABD-XYZ-FFF-01		11.9 ABC	2.1 DEF	0.1 GHI	0.0	56.0	21.2
AB-02113 ABD-XYZ-FFF-02		10.6 ABC	2.2 DEF	0.1 GHI	0.0	44.0	21.1
AB-02114 ABX-XYZ-FFF-01		0.0 ABC	0.0 DEF	0.0 GHI	10.0	42.0	0.9
AB-02115 ABZ-XYZ-FFF-01		11.2 ABC	4.7 DEF	0.1 GHI	0.0	---	21.1
AB-04106 ABX-XYZ-FFF-09		42.0 ABC	0.5 DEF	0.1 GHI	42.0	50.0	84.0
AB-04110 ABX-XYZ-FFF-03		12.5 ABC	7.0 DEF	0.3 GHI	0.0	50.0	24.9
AB-05113 TRE-VSX-TRRR-02		0.0 ABC	0.0 DEF	0.0 GHI	---	50.0	0.1
AB-05157 TRE-VSX-TRRR-05		0.0 ABC	0.0 DEF	0.0 GHI	10.0	36.0	---
AB-06117 YTYT-VMM-TRPP-01		0.0 ABC	0.0 DEF	0.0 GHI	42.0	56.0	0.6
AB-06119 YTYT-VMM-TRPP-05		10.0 ABC	0.7 DEF	0.0 GHI	10.0	27.0	0.2
AB-16120 YTYT-VMM-TRPP-19		10.0 ABC	0.7 DEF	0.0 GHI	10.0	27.0	0.2

TABLE 2

	first 2	full id	Bus	Name	Model
	08	01108		ABC-XYZ-FFF-01
				Text
Device Information							Numbers				N/A	Info
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
AB-01108 ABC-XYZ-FFF-01		C	D	E	T	Unit 1	22	22				John Smith	Date	John Smith
AB-01108 ABC-XYZ-FFF-01		C	J	E	T	Unit 2	0.9	0.9				John Smith	Date	John Smith
AB-01108 ABC-XYZ-FFF-01		C	G	E	T	Unit 3	0.1	0.1				John Smith	Date	John Smith
AB-01108 ABC-XYZ-FFF-01		C	L	E	T	Unit A	22	22				John Smith	Date	John Smith
AB-01108 ABC-XYZ-FFF-01		C	O	E	T	Unit B	22	22				John Smith	Date	John Smith
AB-01108 ABC-XYZ-FFF-01		C	P	E	U	Unit C	14	14				John Smith	Date	John Smith

 

[Dan_W]

Those are very detailed specifications. I can't help but wonder if you might have been able to manually make the workbook yourself quicker than it took to type those instructions out.
You could have just pressed the record macro button as you started to make the workbook, and it would have made the VBA code for you.

Oh well. I'll be interested to see if anyone volunteers to do your work for you.

 

[Blaster1]

Those are very detailed specifications. I can't help but wonder if you might have been able to manually make the workbook yourself quicker than it took to type those instructions out.
You could have just pressed the record macro button as you started to make the workbook, and it would have made the VBA code for you.

Oh well. I'll be interested to see if anyone volunteers to do your work for you.

Thanks Dan, I can do it myself but that would be for one file only. I lack the knowledge to create a logic so it would do the same for all rows as original document contain hundreds of entries.
Thanks anyway, cheers.

 

[Dan_W]

You don't really need to - the Record Macro button will be able to do it for you.