VBA code needed to create multiple individual workbooks using template and table of data

[andyporter1975]

Hello,

If anyone can help me with VBA code for the following it would be greatly appreciated.

I have a table of data which contains multiple rows and columns. Each row relates to a unique business and each column contains data relating to that business.

Using a pre-defined excel doc as a template, I want to be able to automatically populate this template with the info for one businesses into a specific row on the template, save it as the name given in one of the cells into a local folder and then do the same for each and every row within the original table of data.

Is this possible??

Thank you in advance for any hep suggested.

 

[Sacha04Cat]

Hello,

I am sad to not see any reply. I am looking for the same thing. Kind of a merge customer list with a template with formulas and save a workbook per customer with their name in the file format.

Thank you

 

[Hyakkivn]

Hello,

I am sad to not see any reply. I am looking for the same thing. Kind of a merge customer list with a template with formulas and save a workbook per customer with their name in the file format.

Thank you

Although imagination is limitless, but it would help us if we have an example of the datasheet, and it'll be great if you tell us how it should work in detail. So, we can help you easier and faster

 

[andyporter1975]

Although imagination is limitless, but it would help us if we have an example of the datasheet, and it'll be great if you tell us how it should work in detail. So, we can help you easier and faster

 

[Zot]

Let me imagine the flow
You have:
1) Template workbook
2) Data workbook(s) (Probably you have multiple of data workbooks in a specific folder)

I imagine that your Template workbook is where your VBA code would reside.
You run the program. Browse to workbook data folder.
It will copy necessary data from the data workbook into template.
Save the Template workbook as another workbook free of macro.
Clear the template and work on next data workbook perhaps.

Now we have how the template looks like but how the data workbook looks like? Are you looking for specific sheet in the data workbook to extract data from?

 

[Sacha04Cat]

Sorry, I thought Andy has given some info.
Currently we put the customer code in sheet.J3 and save as in a specific folder with the file format in sheet.E2. i's very long with 500 customers
I would run a Macro to leave the template open and do the same thing for the customer list in the second tab. I have listed 10 for this example.
Thank you very much

Tab Sheet

Template.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				File name							Notice: The prices and amounts are for references only and may vary upon billing depending on the departure site and the delivery method. This worksheet is not an official order, Techo-Bloc reserves the right to modify its content without prior notice.
2				030-000419-PANTANO NURSERY
3									000419
4				CONTRACTID			20-USA
5	Code	000419		CUSTITEMRESTRICTIONGROUPID			USA	REP:	GROUP030-Ryan Ravalli				Date
6	Name	PANTANO NURSERY		INVENTSITEID			PEN	Bill to:	000419				TOTAL
7	Shipping	143 FREEHOLD ROAD		PRICEGROUP			US-NJ-SE-NY	PANTANO NURSERY					QTS	LINES		AMOUNT
8	City	MANALAPAN		LINEDISC			US-BKG_00		GROUP030-Ryan Ravalli				0	0		0.00
9	State	NJ		Booking Discount					5%				Authorized			1,600,000.00
10	Code	Description	Origin	Item Restriction Group	Rk	Price	Disc	Price PickUp ORIGIN	Promo %	Promo $	Net Price Origin	UN Sales	ORD QTS	ORD UNIT	UN Sales Per ORD UNIT	AMOUNT
Sheet

Cell Formulas
Range	Formula
E2	E2	=CONCAT(MID(J8,6,3),"-",C5,"-",C6)
H4	H4	=VLOOKUP($C$5,CustGroup[#All],2,FALSE)
H5	H5	=VLOOKUP($C$5,CustGroup[#All],7,FALSE)
H6	H6	=VLOOKUP($C$5,CustGroup[#All],3,FALSE)
J5	J5	=VLOOKUP($C$5,REP[[#All],[CUSTOMERRELATION]:[NAME]],3,FALSE)
J6	J6	=VLOOKUP($C$5,CUST[#All],2,FALSE)
H7	H7	=VLOOKUP($C$5,CustGroup[#All],4,FALSE)
I7	I7	=VLOOKUP($J$6,CUST[#All],3,FALSE)
J8	J8	=VLOOKUP($J$6,REP[[#All],[CUSTOMERRELATION]:[NAME]],3,FALSE)
N8,Q8	N8	=SUM(N11:N1086)
O8	O8	=COUNT(N11:N1086)
C5	C5	=J3
C6	C6	=VLOOKUP($C$5,CUST[#All],3,FALSE)
C7	C7	=VLOOKUP($C$5,CUST[#All],4,FALSE)
C8	C8	=VLOOKUP($C$5,CUST[#All],5,FALSE)
C9	C9	=VLOOKUP($C$5,CUST[#All],6,FALSE)
Q9	Q9	=IFERROR(VLOOKUP(C5,LIMIT,8,FALSE),"Split Bill To Limit")

Named Ranges
Name	Refers To	Cells
LIMIT!Print_Area	=LIMIT!$A$2:$H$217	Q9
Customer!Print_Titles	=Customer!$1:$1	C6:C9, I7, J6
'Customer Save as'!Print_Titles	=Customer!$1:$1	C6:C9, I7, J6
'Cust Price Group'!Query_from_AX	='Cust Price Group'!$A$1:$N$584	H4:H7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H9	Cell Value	=0	text	NO
B8,B10,B1:B4	Cell Value	duplicates	text	NO

 

[Sacha04Cat]

Sorry, I thought Andy has given some info.
Currently we put the customer code in sheet.J3 and save as in a specific folder with the file format in sheet.E2. i's very long with 500 customers
I would run a Macro to leave the template open and do the same thing for the customer list in the second tab. I have listed 10 for this example.
Thank you very much

Tab Sheet

Template.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1				File name							Notice: The prices and amounts are for references only and may vary upon billing depending on the departure site and the delivery method. This worksheet is not an official order, Techo-Bloc reserves the right to modify its content without prior notice.
2				030-000419-PANTANO NURSERY
3									000419
4				CONTRACTID			20-USA
5	Code	000419		CUSTITEMRESTRICTIONGROUPID			USA	REP:	GROUP030-Ryan Ravalli				Date
6	Name	PANTANO NURSERY		INVENTSITEID			PEN	Bill to:	000419				TOTAL
7	Shipping	143 FREEHOLD ROAD		PRICEGROUP			US-NJ-SE-NY	PANTANO NURSERY					QTS	LINES		AMOUNT
8	City	MANALAPAN		LINEDISC			US-BKG_00		GROUP030-Ryan Ravalli				0	0		0.00
9	State	NJ		Booking Discount					5%				Authorized			1,600,000.00
10	Code	Description	Origin	Item Restriction Group	Rk	Price	Disc	Price PickUp ORIGIN	Promo %	Promo $	Net Price Origin	UN Sales	ORD QTS	ORD UNIT	UN Sales Per ORD UNIT	AMOUNT
Sheet

Cell Formulas
Range	Formula
E2	E2	=CONCAT(MID(J8,6,3),"-",C5,"-",C6)
H4	H4	=VLOOKUP($C$5,CustGroup[#All],2,FALSE)
H5	H5	=VLOOKUP($C$5,CustGroup[#All],7,FALSE)
H6	H6	=VLOOKUP($C$5,CustGroup[#All],3,FALSE)
J5	J5	=VLOOKUP($C$5,REP[[#All],[CUSTOMERRELATION]:[NAME]],3,FALSE)
J6	J6	=VLOOKUP($C$5,CUST[#All],2,FALSE)
H7	H7	=VLOOKUP($C$5,CustGroup[#All],4,FALSE)
I7	I7	=VLOOKUP($J$6,CUST[#All],3,FALSE)
J8	J8	=VLOOKUP($J$6,REP[[#All],[CUSTOMERRELATION]:[NAME]],3,FALSE)
N8,Q8	N8	=SUM(N11:N1086)
O8	O8	=COUNT(N11:N1086)
C5	C5	=J3
C6	C6	=VLOOKUP($C$5,CUST[#All],3,FALSE)
C7	C7	=VLOOKUP($C$5,CUST[#All],4,FALSE)
C8	C8	=VLOOKUP($C$5,CUST[#All],5,FALSE)
C9	C9	=VLOOKUP($C$5,CUST[#All],6,FALSE)
Q9	Q9	=IFERROR(VLOOKUP(C5,LIMIT,8,FALSE),"Split Bill To Limit")

Named Ranges
Name	Refers To	Cells
LIMIT!Print_Area	=LIMIT!$A$2:$H$217	Q9
Customer!Print_Titles	=Customer!$1:$1	C6:C9, I7, J6
'Customer Save as'!Print_Titles	=Customer!$1:$1	C6:C9, I7, J6
'Cust Price Group'!Query_from_AX	='Cust Price Group'!$A$1:$N$584	H4:H7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H9	Cell Value	=0	text	NO
B8,B10,B1:B4	Cell Value	duplicates	text	NO

second tab, customer list

Template.xlsm
	A	B	C
1	Customer account	Invoice account	Name
2	000775	000775	447 CORNERSTONE PRODUCTS
3	000442	000442	9 BROTHERS BUILDING SUPPLY CORP.
4	001971	000442	9 BROTHERS BUILDING SUPPLY CORP.
5	000664	000664	903 LANDSCAPE SUPPLY INC
6	002581	002581	A LOT MASON SUPPLY CORP
7	000001	000001	A&B KEARNS TRKG & STONE CENTER
8	002578	000001	A&B KEARNS TRKG & STONE CENTER
9	002356	002356	A, B & D ENTERPRISES LLC / DBA BEDROCK GARDENS
10	000419	000419	PANTANO NURSERY
11	000157	000157	DEMARCO BROTHERS INC
Customer Save as

 

[Sacha04Cat]

Let me imagine the flow
You have:
1) Template workbook
2) Data workbook(s) (Probably you have multiple of data workbooks in a specific folder)

I imagine that your Template workbook is where your VBA code would reside.
You run the program. Browse to workbook data folder.
It will copy necessary data from the data workbook into template.
Save the Template workbook as another workbook free of macro.
Clear the template and work on next data workbook perhaps.

Now we have how the template looks like but how the data workbook looks like? Are you looking for specific sheet in the data workbook to extract data fr

 

[Sacha04Cat]

Hello, currently, I hide the sheets that I don't want the customer see, I put security on the sheets and the workbook and save as for everyone.
May be we can copy only the first sheet with the formulas with no error for the one linked to the others sheets ? we break the link with past value ?

Thank you

 

[Hyakkivn]

View attachment 46484

1st. What does your "Demo table data" look like? And does it have same shape with template or not ?
2nd. Are your template and your Store data in the same workbook or they are separated? (you save store data to new workbook)