VBA to create new worksheets from a template, populate the new worksheet by copying data from another sheet to specific locations.

[BigPaws]

Hello All You VBA Experts,

Like so many others, I am relatively new to VBA. I have a code that will take Test Case data from my "Working", create new a worksheet from the Template, name it and populate it correctly. However, it will only add one Test Case with up to four Steps, I need to be able to add multiple Test Cases and multiple steps to each new spreadsheet. On the attached samples I have added color coding just to help demonstrate where the data on the Working sheet would populate on the new template. The Working Overview image is a zoomed out view of the data on the Working sheet. The Working Close-Up gives you a better look at the data and headers. Each row on the Working sheet is a Test Case, the columns after L are for the steps of the test case. All Test Cases with the same TabName (column A) and/or Plan information (columns B-D) will be on the same worksheet. Columns A through D will all be the same for all Test Cases that will go on the same worksheet. This example would have four new worksheets created with four test cases on each one and looking like the Completed Sample image when done. You'll notice on the Template the test cases are now in columns and the step information is now in the rows. A Plan/worksheet can gave 1 to over 100 test cases and 2 to over 20 steps. It is never a set amount. Any help would be greatly appreciated.

VBA Template-Sample.xlsx
	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
1	TabName	PlanNm	PlanAlias	PlanDescription	TEST	CRITERIA1	CRITERIA2	CRITERIA3	TEST CASE	Alias	Description	STEP	STEP 01	System/Application	Role (if applicable)	Instructions	Expected Outcomes	Data to Collect	Screen Capture?	Attachment?	Requirements		STEP 02	System/Application	Role (if applicable)	Instructions	Expected Outcomes	Data to Collect	Screen Capture?	Attachment?	Requirements		STEP 03	System/Application	Role (if applicable)	Instructions	Expected Outcomes	Data to Collect	Screen Capture?	Attachment?	Requirements		STEP 04	System/Application	Role (if applicable)	Instructions	Expected Outcomes	Data to Collect	Screen Capture?	Attachment?	Requirements
2	BD Fx-1	Microbiology BD Fx-1	MIC-BDF1	CCV3 Microbiology BD Fx-1					AV-Culture, Mycobacteria, Blood-Final AV 1	1	Culture, Mycobacteria, Blood BTEC1 Method [123111] Final Auto Verification		AV			Final auto verification in Beaker for BTEC1 Method [123111]-Culture, Mycobacteria, Blood	Final Verification: Lab MC Mycobacteria Blood Final AV [755887]	Spec ID	X
3	BD Fx-1	Microbiology BD Fx-1	MIC-BDF1	CCV3 Microbiology BD Fx-1					AV-Culture, Mycobacteria, Blood-Final AV 2	2	Culture, Mycobacteria, Blood BTEC1 Method [123111] Final Auto Verification		AV			Final auto verification in Beaker for BTEC1 Method [123111]-Culture, Mycobacteria, Blood	Final Verification: Lab MC Mycobacteria Blood Final AV [755887]	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X
4	BD Fx-1	Microbiology BD Fx-1	MIC-BDF1	CCV3 Microbiology BD Fx-1					AV-Culture, Mycobacteria, Blood-Final AV 3	3	Culture, Mycobacteria, Blood BTEC1 Method [123111] Final Auto Verification		AV			Final auto verification in Beaker for BTEC1 Method [123111]-Culture, Mycobacteria, Blood	Final Verification: Lab MC Mycobacteria Blood Final AV [755887]	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X				Negative			Run Negative QC	Results appear as expected	Spec ID	X
5	BD Fx-1	Microbiology BD Fx-1	MIC-BDF1	CCV3 Microbiology BD Fx-1					AV-Culture, Mycobacteria, Blood-Final AV 4	4	Culture, Mycobacteria, Blood BTEC1 Method [123111] Final Auto Verification		AV			Final auto verification in Beaker for BTEC1 Method [123111]-Culture, Mycobacteria, Blood	Final Verification: Lab MC Mycobacteria Blood Final AV [755887]	Spec ID	X
6	BD Fx-2	Microbiology BD Fx-2	MIC-BDF2	CCV3 Microbiology BD Fx-2					Blood culture 1	1	Range and AMR validation of Blood culture		Process			Run both positive and negative specimens Receive and process specimen Confirm label readability Load the bottles Check the negative updates Update the positive (refer to BLC under LabPro)	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X
7	BD Fx-2	Microbiology BD Fx-2	MIC-BDF2	CCV3 Microbiology BD Fx-2					Blood culture 2	2	Range and AMR validation of Blood culture		Process			Run both positive and negative specimens Receive and process specimen Confirm label readability Load the bottles Check the negative updates Update the positive (refer to BLC under LabPro)	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X
8	BD Fx-2	Microbiology BD Fx-2	MIC-BDF2	CCV3 Microbiology BD Fx-2					Blood culture 3	3	Range and AMR validation of Blood culture		Process			Run both positive and negative specimens Receive and process specimen Confirm label readability Load the bottles Check the negative updates Update the positive (refer to BLC under LabPro)	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X
9	BD Fx-2	Microbiology BD Fx-2	MIC-BDF2	CCV3 Microbiology BD Fx-2					Blood culture 4	4	Range and AMR validation of Blood culture		Process			Run both positive and negative specimens Receive and process specimen Confirm label readability Load the bottles Check the negative updates Update the positive (refer to BLC under LabPro)	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X
10	DiaSorin1	Microbiology DiaSorin1	MIC-DSN1	CCV3 Microbiology DiaSorin1					QC -Adenovirus 1	1	High Positive, Low Positive and Negative DiaSorin1 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X				Negative			Run Negative QC	Results appear as expected	Spec ID	X
11	DiaSorin1	Microbiology DiaSorin1	MIC-DSN1	CCV3 Microbiology DiaSorin1					QC -Adenovirus 2	2	High Positive, Low Positive and Negative DiaSorin1 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X
12	DiaSorin1	Microbiology DiaSorin1	MIC-DSN1	CCV3 Microbiology DiaSorin1					QC -Adenovirus 3	3	High Positive, Low Positive and Negative DiaSorin1 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X				Negative			Run Negative QC	Results appear as expected	Spec ID	X
13	DiaSorin1	Microbiology DiaSorin1	MIC-DSN1	CCV3 Microbiology DiaSorin1					QC -Adenovirus 4	4	High Positive, Low Positive and Negative DiaSorin1 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X				Negative			Run Negative QC	Results appear as expected	Spec ID	X
14	DiaSorin2	Microbiology DiaSorin2	MIC-DSN2	CCV3 Microbiology DiaSorin2					QC -BK 1	1	High Positive, Low Positive and Negative DiaSorin2 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X
15	DiaSorin2	Microbiology DiaSorin2	MIC-DSN2	CCV3 Microbiology DiaSorin2					QC -BK 2	2	High Positive, Low Positive and Negative DiaSorin2 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X				Negative			Run Negative QC	Results appear as expected	Spec ID	X				OK	LIS		Run OK QC	Results appear as expected	Spec ID	X	X
16	DiaSorin2	Microbiology DiaSorin2	MIC-DSN2	CCV3 Microbiology DiaSorin2					QC -BK 3	3	High Positive, Low Positive and Negative DiaSorin2 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X
17	DiaSorin2	Microbiology DiaSorin2	MIC-DSN2	CCV3 Microbiology DiaSorin2					QC -BK 4	4	High Positive, Low Positive and Negative DiaSorin2 QC		High			Run High Positive QC	Results appear as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X				Negative			Run Negative QC	Results appear as expected	Spec ID	X				OK	LIS		Run OK QC	Results appear as expected	Spec ID	X	X
18	DiaSorin3	Microbiology DiaSorin3	MIC-DSN3	CCV3 Microbiology DiaSorin3					AV-BK Virus PCR Blood-Prevented AV 1	1	BK Virus PCR Blood Diasorin 3 [123105] Auto Verification Prevented		AV			Prevented auto verification in Beaker for Diasorin 3 [123105]-BK Virus PCR Blood	Test Auto Verification Prevented (Rule: MM Molecular Micro Prevent Final AV Rule [753520])	Spec ID	X
19	DiaSorin3	Microbiology DiaSorin3	MIC-DSN3	CCV3 Microbiology DiaSorin3					AV-BK Virus PCR Blood-Prevented AV 2	2	BK Virus PCR Blood Diasorin 3 [123105] Auto Verification Prevented		AV			Prevented auto verification in Beaker for Diasorin 3 [123105]-BK Virus PCR Blood	Test Auto Verification Prevented (Rule: MM Molecular Micro Prevent Final AV Rule [753520])	Spec ID	X
20	DiaSorin3	Microbiology DiaSorin3	MIC-DSN3	CCV3 Microbiology DiaSorin3					AV-BK Virus PCR Blood-Prevented AV 3	3	BK Virus PCR Blood Diasorin 3 [123105] Auto Verification Prevented		AV			Prevented auto verification in Beaker for Diasorin 3 [123105]-BK Virus PCR Blood	Test Auto Verification Prevented (Rule: MM Molecular Micro Prevent Final AV Rule [753520])	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X				Negative			Run Negative QC	Results appear as expected	Spec ID	X				OK	LIS		Run OK QC	Results appear as expected	Spec ID	X	X
21	DiaSorin3	Microbiology DiaSorin3	MIC-DSN3	CCV3 Microbiology DiaSorin3					AV-BK Virus PCR Blood-Prevented AV 4	4	BK Virus PCR Blood Diasorin 3 [123105] Auto Verification Prevented		AV			Prevented auto verification in Beaker for Diasorin 3 [123105]-BK Virus PCR Blood	Test Auto Verification Prevented (Rule: MM Molecular Micro Prevent Final AV Rule [753520])	Spec ID	X
22	DiaSorin4	Microbiology DiaSorin4	MIC-DSN4	CCV3 Microbiology DiaSorin4					EBV 1	1	Range and AMR validation of EBV		Process			Receive and process specimen Confirm label readability Load and run specimen Review and upload results to DI Review results in Beaker	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X
23	DiaSorin4	Microbiology DiaSorin4	MIC-DSN4	CCV3 Microbiology DiaSorin4					EBV 2	2	Range and AMR validation of EBV		Process			Receive and process specimen Confirm label readability Load and run specimen Review and upload results to DI Review results in Beaker	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X
24	DiaSorin4	Microbiology DiaSorin4	MIC-DSN4	CCV3 Microbiology DiaSorin4					EBV 3	3	Range and AMR validation of EBV		Process			Receive and process specimen Confirm label readability Load and run specimen Review and upload results to DI Review results in Beaker	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X				Low			Low Positive QC	Results appear as expected	Spec ID	X
25	DiaSorin4	Microbiology DiaSorin4	MIC-DSN4	CCV3 Microbiology DiaSorin4					EBV 4	4	Range and AMR validation of EBV		Process			Receive and process specimen Confirm label readability Load and run specimen Review and upload results to DI Review results in Beaker	Accepts and transmit results to Beaker if meeting criteria Results translate as expected	Spec ID	X
Working

VBA Template-Sample.xlsx
	A	B	C	D	E	F	G	H	I
1
2	TEST PLAN	01-TEMPLATE
3	Alias	ALIAS
4	Description	01-TEMPLATE Validation Testing
5
6	TEST CASE	My Sample Testcase
7	Alias	000
8	Description	Description
9	STEP
10	STEP 01	Step
11	System/Application	WPE CP
12	Role (if applicable)	Pathologist
13	Instructions	Instructions
14	Expected Outcomes	Outcomes
15	Data to Collect	Data
16	Screen Capture?	X
17	Attachment?	X
18	Requirements	CP1.A.3|Receive Order Comms from ICE
19
20	STEP 02
21	System/Application
22	Role (if applicable)
23	Instructions
24	Expected Outcomes
25	Data to Collect
26	Screen Capture?
27	Attachment?
28	Requirements
29
30	STEP 03
31	System/Application
32	Role (if applicable)
33	Instructions
34	Expected Outcomes
35	Data to Collect
36	Screen Capture?
37	Attachment?
38	Requirements
39
40	STEP 04
41	System/Application
42	Role (if applicable)
43	Instructions
44	Expected Outcomes
45	Data to Collect
46	Screen Capture?
47	Attachment?
48	Requirements
49
50	STEP 05
51	System/Application
52	Role (if applicable)
53	Instructions
54	Expected Outcomes
55	Data to Collect
56	Screen Capture?
57	Attachment?
58	Requirements
59
60	STEP 06
61	System/Application
62	Role (if applicable)
63	Instructions
64	Expected Outcomes
65	Data to Collect
66	Screen Capture?
67	Attachment?
68	Requirements
69
Template