How to copy a specific rows if met the conditions and paste it to another sheet using macro excel formula

[PreciousM]

Hi! I am not expert to do macro in excel but hoping someone out there could help me with the formula. I have two worksheets:
Sheet1 - raw data
Sheet2 - enrollment report

The button that has the macro formula is in the Sheet1. Once i click the button, the macro will run with first condition that if row B2 in Sheet1 has value, it should copy row B2 to X2 and paste it to second row (after the header) of Sheet2. But if none, it should check the next row and run the formula and goes on.

Next condition is that, if row Y2 (Do you wish to enroll another dependent (3rd, in the hierarchy line)?:Enrollment Details) is equal to 'Yes', it should copy row B2 to N2 and row AA2 to AI2. This same goes for row AJ2, AU2, BF2, BQ2, and CB2. But if the answer is 'No' or has no value, it will check the next row in Sheet1 then run again the same copy formula until the last row.

I have attached here a sample of my excel file. Hope this will helps. And really hoping someone could help me. Thanks!

Sheet2:

sample report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee Number	Employee Last Name	Employee Given Name	Employee Middle Name	Employee M.I	Employee Ext (Jr., II, etc.)	Employee Civil Status	Enrollment Classification	Benefit Plan	Enrollment Eligibilities	Dependent Last Name	Dependent Given Name	Dependent Middle Name	Dependent M.I	Dependent Ext. (Jr., II, etc.)	Relationship	Dependent Date of Birth	Dependent Sex	Dependent Civil Status
2	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married
3	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
4	2468	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist	Regular Private | P 150,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/1969	Female	Widow/Widower
5	3690	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist	Regular Private | 150,000.00
6	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married
7	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
8	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dhannica Marie	May	Z		Sister	5/4/2006	Female	Single
enrollment report format

sample report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee Number	Employee Last Name	Employee Given Name	Employee Middle Name	Employee M.I	Employee Ext (Jr., II, etc.)	Employee Civil Status	Enrollment Classification	Benefit Plan	Enrollment Eligibilities	Dependent Last Name	Dependent Given Name	Dependent Middle Name	Dependent M.I	Dependent Ext. (Jr., II, etc.)	Relationship	Dependent Date of Birth	Dependent Sex	Dependent Civil Status
2	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married
3	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
4	2468	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist	Regular Private | P 150,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/1969	Female	Widow/Widower
5	3690	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist	Regular Private | 150,000.00
6	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married
7	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
8	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dhannica Marie	May	Z		Sister	5/4/2006	Female	Single
enrollment report format

Sheet1:

Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â

Employee ID Number

Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â

Employee ID Number

Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â

Employee ID Number

Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â

Employee ID Number

Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â

Enrollment Classification(Enrollment classification will determine the available benefit plan options)

Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)

Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)

Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)

Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)

Enrollment Eligibilities:Enrollment Details

Last Name:Dependent 1:Dependent Information

Given Name:Dependent 1:Dependent Information

Middle Name:Dependent 1:Dependent Information

M.I. to Use:Dependent 1:Dependent Information

Ext. (Jr., II, etc.):Dependent 1:Dependent Information

Relationship:Enrollment Details

Date of Birth:Enrollment Details

Sex:Enrollment Details

Civil Status:Enrollment Details

Do you wish to enroll another dependent (2nd, in the hierarchy line)?:Enrollment Details

Enrollment Eligibilities:Enrollment Details

Last Name:Dependent 2:Dependent Information

Given Name:Dependent 2:Dependent Information

Middle Name:Dependent 2:Dependent Information

M.I. to Use:Dependent 2:Dependent Information

Ext. (Jr., II, etc.):Dependent 2:Dependent Information

Relationship:Enrollment Details

Date of Birth:Enrollment Details

Sex:Enrollment Details

Civil Status:Enrollment Details

Do you wish to enroll another dependent (3rd, in the hierarchy line)?:Enrollment Details

Enrollment Eligibilities:Enrollment Details

Last Name:Dependent 3:Dependent Information

Given Name:Dependent 3:Dependent Information

Middle Name:Dependent 3:Dependent Information

M.I. to Use:Dependent 3:Dependent Information

Ext. (Jr., II, etc.):Dependent 3:Dependent Information

Relationship:Enrollment Details

Date of Birth:Enrollment Details

Sex:Enrollment Details

Civil Status:Enrollment Details

Do you wish to enroll another dependent (4th, in the hierarchy line)?:Enrollment Details

Enrollment Eligibilities:Enrollment Details

Last Name:Dependent 4:Dependent Information

Given Name:Dependent 4:Dependent Information

Middle Name:Dependent 4:Dependent Information

M.I. to Use:Dependent 4:Dependent Information

Ext. (Jr., II, etc.):Dependent 4:Dependent Information

Relationship:Enrollment Details

Date of Birth:Enrollment Details

Sex:Enrollment Details

Civil Status:Enrollment Details

Do you wish to enroll another dependent (5th, in the hierarchy line)?:Enrollment Details

Enrollment Eligibilities:Enrollment Details

Last Name:Dependent 5:Dependent Information

Given Name:Dependent 5:Dependent Information

Middle Name:Dependent 5:Dependent Information

M.I. to Use:Dependent 5:Dependent Information

Ext. (Jr., II, etc.):Dependent 5:Dependent Information

Relationship:Enrollment Details

Date of Birth:Enrollment Details

Sex:Enrollment Details

Civil Status:Enrollment Details

Do you wish to enroll another dependent (6th, in the hierarchy line)?:Enrollment Details

Enrollment Eligibilities:Enrollment Details

Last Name:Dependent 6:Dependent Information

Given Name:Dependent 6:Dependent Information

Middle Name:Dependent 6:Dependent Information

M.I. to Use:Dependent 6:Dependent Information

Ext. (Jr., II, etc.):Dependent 6:Dependent Information

Relationship:Enrollment Details

Date of Birth:Enrollment Details

Sex:Enrollment Details

Civil Status:Enrollment Details

Do you wish to enroll another dependent (7th, in the hierarchy line)? Please email GSMClinic@bakermckenzie.com.:Enrollment Details

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1234​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1234​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1234​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1234​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

Dependents of Supervisor | Specialist

Regular Private | 250,000.00

[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings

Alivarez

Fazima

Angelesi

A

Mother

10/14/1966​

Female

Married

Yes

[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings

Alivarez

Mia Fortuna

Angelesi

A

Sister

1/5/2004​

Female

Single

No

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

2468​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

2468​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

2468​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

2468​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

Dependents of Supervisor | Specialist

Regular Private | 150,000.00

[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings

Nablos

Asunciona

Guccena

G

Mother

8/14/2021​

Female

Widow/Widower

No

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

3690​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

3690​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

3690​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

3690​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

Dependents of Supervisor | Specialist

Regular Private | 150,000.00

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1089​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1089​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1089​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

1089​

Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.

Dependents of Supervisor | Specialist

Regular Private | 350,000.00

[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings

Maharlika

Seanti

Deanna

D

Father

4/25/1958​

Male

Married

Yes

[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings

Maharlika

Dharling Erika

May

Z

Sister

5/4/2013​

Female

Single

Yes

[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings

Maharlika

Dhannica Marie

May

Z

Sister

5/4/2006​

Female

Single

No

 

[airforceone]

I did try the last piece of code and it's working on my end, I'm using your sample data from your first post...
Hope you can somehow send me a copy of your actual records (even 5 rows/records will do)

 

[PreciousM]

I hope I can help more, would you be kind enough to include your table (sheet1)
your sample record is incomplete so I can test it in your actual records and see if I can further help you...
Also, can you take an screenshot of your expected result (before and after processing)

Hi airforceone, Thank you so much in advance. Please see below:

Sheet 1:

sample report.xlsx
	AJ
2
3
raw data

Sheet 2: Destination of copied data from Sheet1

sample report.xlsx
	A
1	Employee Number
enrollment report format

What should be the result after the copy and paste:

sample report.xlsx
	C
11
enrollment report format

sample report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee Number	Employee Last Name	Employee Given Name	Employee Middle Name	Employee M.I	Employee Ext (Jr., II, etc.)	Employee Civil Status	Enrollment Classification	Benefit Plan	Enrollment Eligibilities	Dependent Last Name	Dependent Given Name	Dependent Middle Name	Dependent M.I	Dependent Ext. (Jr., II, etc.)	Relationship	Dependent Date of Birth	Dependent Sex	Dependent Civil Status
2	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married
3	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
4	2468	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist	Regular Private | P 150,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/1969	Female	Widow/Widower
5	3690	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist	Regular Private | 150,000.00
6	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married
7	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
8	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dhannica Marie	May	Z		Sister	5/4/2006	Female	Single
enrollment report format

 

[PreciousM]

[UPDATED] Hi airforceone, Thank you so much in advance. Please see below:

Sheet 1:

sample report.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
1	Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â	Employee ID Number	Please enter your office email address to receive a copy of the enrollment form.	Last Name:Complete Name :Employee Name	Given Name:Complete Name :Employee Name	Middle Name:Complete Name :Employee Name	Middle Initial (M.I to use for card printing):Complete Name :Employee Name	Ext (Jr., II, etc.):Complete Name :Employee Name	Employee's Current Civil Status	Enrollment Classification(Enrollment classification will determine the available benefit plan options)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Enrollment Eligibilities:Enrollment Details	Last Name:Dependent 1:Dependent Information	Given Name:Dependent 1:Dependent Information	Middle Name:Dependent 1:Dependent Information	M.I. to Use:Dependent 1:Dependent Information	Ext. (Jr., II, etc.):Dependent 1:Dependent Information	Relationship:Enrollment Details	Date of Birth:Enrollment Details	Sex:Enrollment Details	Civil Status:Enrollment Details	Do you wish to enroll another dependent (2nd, in the hierarchy line)?:Enrollment Details	Enrollment Eligibilities:Enrollment Details	Last Name:Dependent 2:Dependent Information	Given Name:Dependent 2:Dependent Information	Middle Name:Dependent 2:Dependent Information	M.I. to Use:Dependent 2:Dependent Information	Ext. (Jr., II, etc.):Dependent 2:Dependent Information	Relationship:Enrollment Details	Date of Birth:Enrollment Details	Sex:Enrollment Details	Civil Status:Enrollment Details
2
3
4	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	1234	me1234@yahoo.com	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist			Regular Private | 250,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married	Yes	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
5	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	2468	elias6789@yahoo.com	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/2021	Female	Widow/Widower	No
6
7	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	3690	yeahboy@yahoo.com	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00
8
9
10	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	1089	gandaghorl@yahoo.com	Maharlika	Dhannie	May	Z		Single	Dependents of Supervisor | Specialist			Regular Private | 350,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married	Yes	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
raw data

(Blank) Sheet 2: Destination of copied data from Sheet1

sample report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee Number	Employee Last Name	Employee Given Name	Employee Middle Name	Employee M.I	Employee Ext (Jr., II, etc.)	Employee Civil Status	Enrollment Classification	Benefit Plan	Enrollment Eligibilities	Dependent Last Name	Dependent Given Name	Dependent Middle Name	Dependent M.I	Dependent Ext. (Jr., II, etc.)	Relationship	Dependent Date of Birth	Dependent Sex	Dependent Civil Status
2
3
4
5
6
7
enrollment report format

What should be the result after the copy and paste:

sample report.xlsx
	C
11
enrollment report format

sample report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee Number	Employee Last Name	Employee Given Name	Employee Middle Name	Employee M.I	Employee Ext (Jr., II, etc.)	Employee Civil Status	Enrollment Classification	Benefit Plan	Enrollment Eligibilities	Dependent Last Name	Dependent Given Name	Dependent Middle Name	Dependent M.I	Dependent Ext. (Jr., II, etc.)	Relationship	Dependent Date of Birth	Dependent Sex	Dependent Civil Status
2	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married
3	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
4	2468	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist	Regular Private | P 150,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/1969	Female	Widow/Widower
5	3690	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist	Regular Private | 150,000.00
6	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married
7	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
8	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dhannica Marie	May	Z		Sister	5/4/2006	Female	Single
enrollment report format

Any help will be appreciated as you've help me so much already. Thank you thank you!

 

[airforceone]

Hi airforceone, Thank you so much in advance. Please see below:

Sheet 1:

sample report.xlsx
	AJ
2
3
raw data

Sheet 2: Destination of copied data from Sheet1

sample report.xlsx
	A
1	Employee Number
enrollment report format

What should be the result after the copy and paste:

sample report.xlsx
	C
11
enrollment report format

sample report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee Number	Employee Last Name	Employee Given Name	Employee Middle Name	Employee M.I	Employee Ext (Jr., II, etc.)	Employee Civil Status	Enrollment Classification	Benefit Plan	Enrollment Eligibilities	Dependent Last Name	Dependent Given Name	Dependent Middle Name	Dependent M.I	Dependent Ext. (Jr., II, etc.)	Relationship	Dependent Date of Birth	Dependent Sex	Dependent Civil Status
2	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married
3	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
4	2468	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist	Regular Private | P 150,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/1969	Female	Widow/Widower
5	3690	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist	Regular Private | 150,000.00
6	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married
7	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
8	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dhannica Marie	May	Z		Sister	5/4/2006	Female	Single
enrollment report format

I would like to extend my help if only you could give more information...
I am asking for a actual/dummy (5 records will do) with the right sets of information. you can replace all data in your sheet if you want (for security purpose) as long as the needed/target columns are included and relevant value is in place

1. the sample you've given is only up to column "S", while in your condition 2: if Column Y "Enroll Again ?" if "YES"

2. Is your sheet2 static or dynamically created? meaning when you open the workbook sheet2 is already included or is it created with the VBA implementation?

I hope I made it clear and to be of help to you...

 

[PreciousM]

I would like to extend my help if only you could give more information...
I am asking for a actual/dummy (5 records will do) with the right sets of information. you can replace all data in your sheet if you want (for security purpose) as long as the needed/target columns are included and relevant value is in place

1. the sample you've given is only up to column "S", while in your condition 2: if Column Y "Enroll Again ?" if "YES"

2. Is your sheet2 static or dynamically created? meaning when you open the workbook sheet2 is already included or is it created with the VBA implementation?

I hope I made it clear and to be of help to you...

Hello, sorry for the confusion. Below are the details:

Basically in Sheet1, each row contains the employee details and dependent details. What i needed to do is to have it format to copy data of an employee, B:M of Sheet1 to Sheet2. Then below that data contains the employee's dependents.

Condition 1: If cell B2 in Sheet1 (raw data) has value, it should copy cell B2 to X2 and paste it to cell B2 (after the header) of Sheet2. It should skip and do not copy blank cells.

Sheet1:

sample report.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
1	Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â	Employee ID Number	Please enter your office email address to receive a copy of the enrollment form.	Last Name:Complete Name :Employee Name	Given Name:Complete Name :Employee Name	Middle Name:Complete Name :Employee Name	Middle Initial (M.I to use for card printing):Complete Name :Employee Name	Ext (Jr., II, etc.):Complete Name :Employee Name	Employee's Current Civil Status	Enrollment Classification(Enrollment classification will determine the available benefit plan options)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Enrollment Eligibilities:Enrollment Details	Last Name:Dependent 1:Dependent Information	Given Name:Dependent 1:Dependent Information	Middle Name:Dependent 1:Dependent Information	M.I. to Use:Dependent 1:Dependent Information	Ext. (Jr., II, etc.):Dependent 1:Dependent Information	Relationship:Enrollment Details	Date of Birth:Enrollment Details	Sex:Enrollment Details	Civil Status:Enrollment Details	Do you wish to enroll another dependent (2nd, in the hierarchy line)?:Enrollment Details	Enrollment Eligibilities:Enrollment Details	Last Name:Dependent 2:Dependent Information	Given Name:Dependent 2:Dependent Information	Middle Name:Dependent 2:Dependent Information	M.I. to Use:Dependent 2:Dependent Information	Ext. (Jr., II, etc.):Dependent 2:Dependent Information	Relationship:Enrollment Details	Date of Birth:Enrollment Details	Sex:Enrollment Details	Civil Status:Enrollment Details
2
3
4	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	1234	me1234@yahoo.com	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist			Regular Private | 250,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married	Yes	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
5	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	2468	elias6789@yahoo.com	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/2021	Female	Widow/Widower	No
6
7	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	3690	yeahboy@yahoo.com	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00
8
9
10	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	1089	gandaghorl@yahoo.com	Maharlika	Dhannie	May	Z		Single	Dependents of Supervisor | Specialist			Regular Private | 350,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married	Yes	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
raw data

Sheet2: (enrollment report format) this is already formatted with the headers:

sample report.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Employee Number	Employee Last Name	Employee Given Name	Employee Middle Name	Employee M.I	Employee Ext (Jr., II, etc.)	Employee Civil Status	Enrollment Classification	Benefit Plan	Enrollment Eligibilities	Dependent Last Name	Dependent Given Name	Dependent Middle Name	Dependent M.I	Dependent Ext. (Jr., II, etc.)	Relationship	Dependent Date of Birth	Dependent Sex	Dependent Civil Status
2	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married
3	1234	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist	Regular Private | P 250,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	Single
4	2468	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist	Regular Private | P 150,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/1969	Female	Widow/Widower
5	3690	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist	Regular Private | 150,000.00
6	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married
7	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dharling Erika	May	Z		Sister	5/4/2013	Female	Single
8	1089	Maharlika	Dhannie Emo	May	Z		Single	Dependents of Expert | Manager	Regular Private | 450,000.00	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Dhannica Marie	May	Z		Sister	5/4/2006	Female	Single
enrollment report format

Condition 2: If cell Y2 of Sheet1 (Do you wish to enroll another dependent (3rd, in the hierarchy line)?:Enrollment Details) is equal to 'Yes', it should copy row B2 to N2 and row AA2 to AI2.

Though to answer your clarifications:

1. the sample you've given is only up to column "S", while in your condition 2: if Column Y "Enroll Again ?" if "YES"
- the sheet that has only until column S is the Sheet2 which is the destination of copied data from Sheet1
- the data that has until column Y is in the Sheet1 where has the validation of "enroll again"

2. Is your sheet2 static or dynamically created? meaning when you open the workbook sheet2 is already included or is it created with the VBA implementation? - the sheet 2 is already created and formatted that way, it is already included in the workbook.

I hope this helps. Let me know if you need anything else for me. Thank you, thank you!

 

[airforceone]

Condition 1:
If cell B2 in Sheet1 (raw data) has value, it should copy cell B2 to X2 and paste it to cell B2 (after the header) of Sheet2. It should skip and do not copy blank cells.
Condition 2:
If cell Y2 of Sheet1 (Do you wish to enroll another dependent (3rd, in the hierarchy line)?:Enrollment Details) is equal to 'Yes', it should copy row B2 to N2 and row AA2 to AI2.

Sheet1 consist of Columns A - AI (35 columns)
Columns B - X (23 columns)
Columns B - N (13 columns)
Columns AA - AI (9 columns)

Sheet2 consist of Columns A - S (19 columns)

based on your Condition Sheet2 needs more columns as target from Sheet1!
Are all copied columns will go to Sheet2, right?
if so, there will be inter lapping cells from Sheet1 to Sheet2? I don't know if what my observation is correct?
if not, then what Columns will Sheet2 be receiving?
and for the code, I tested again with your sample data and it works just fine! included here is the code I used and sample revised data (I included more empty cells to test my code).
for the would be copied columns and target Sheet (Sheet2) adjust accordingly.
Update me with screenshot if possible, so we can assess what's need or what went wrong....

Download my Sample Table (always scan it with your available antivirus)

Sub A_PROB_102_CODE_04_PreciousM()
    Dim Row4Sheet2 As Integer, LRow As Long, iCTR As Integer, WSCopy As Worksheet
    Set WSCopy = ActiveSheet
    ActiveSheet.Select
    'Count Row even with Empty Cells
    LRow = ActiveSheet.UsedRange.rows.Count + ActiveSheet.UsedRange.rows(1).Row
    iCTR = 2
    Row4Sheet2 = 2
    Do While iCTR < LRow
        If Not IsEmpty(Cells(iCTR, 2)) Then
            WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
            If UCase(Cells(iCTR, 25)) = "YES" Then
                WSCopy.Range("AA2" & iCTR & ":AI" & iCTR).Copy Worksheets("PAGE02").Range("K" & Row4Sheet2 & ":S" & Row4Sheet2)
            End If
            Row4Sheet2 = Row4Sheet2 + 1
        End If
        iCTR = iCTR + 1
    Loop
End Sub

 

[PreciousM]

Condition 1:
If cell B2 in Sheet1 (raw data) has value, it should copy cell B2 to X2 and paste it to cell B2 (after the header) of Sheet2. It should skip and do not copy blank cells.
Condition 2:
If cell Y2 of Sheet1 (Do you wish to enroll another dependent (3rd, in the hierarchy line)?:Enrollment Details) is equal to 'Yes', it should copy row B2 to N2 and row AA2 to AI2.

Sheet1 consist of Columns A - AI (35 columns)
Columns B - X (23 columns)
Columns B - N (13 columns)
Columns AA - AI (9 columns)

Sheet2 consist of Columns A - S (19 columns)

based on your Condition Sheet2 needs more columns as target from Sheet1!
Are all copied columns will go to Sheet2, right?
if so, there will be inter lapping cells from Sheet1 to Sheet2? I don't know if what my observation is correct?
if not, then what Columns will Sheet2 be receiving?
and for the code, I tested again with your sample data and it works just fine! included here is the code I used and sample revised data (I included more empty cells to test my code).
for the would be copied columns and target Sheet (Sheet2) adjust accordingly.
Update me with screenshot if possible, so we can assess what's need or what went wrong....

Download my Sample Table (always scan it with your available antivirus)

Sub A_PROB_102_CODE_04_PreciousM()
    Dim Row4Sheet2 As Integer, LRow As Long, iCTR As Integer, WSCopy As Worksheet
    Set WSCopy = ActiveSheet
    ActiveSheet.Select
    'Count Row even with Empty Cells
    LRow = ActiveSheet.UsedRange.rows.Count + ActiveSheet.UsedRange.rows(1).Row
    iCTR = 2
    Row4Sheet2 = 2
    Do While iCTR < LRow
        If Not IsEmpty(Cells(iCTR, 2)) Then
            WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
            If UCase(Cells(iCTR, 25)) = "YES" Then
                WSCopy.Range("AA2" & iCTR & ":AI" & iCTR).Copy Worksheets("PAGE02").Range("K" & Row4Sheet2 & ":S" & Row4Sheet2)
            End If
            Row4Sheet2 = Row4Sheet2 + 1
        End If
        iCTR = iCTR + 1
    Loop
End Sub

Thank you for this Please see my answers on your below questions:

based on your Condition Sheet2 needs more columns as target from Sheet1 - I am not sure if i get this right, but all i wanted is to format the copied raw data from Sheet1 to Sheet2. As you can see in Sheet1, the details of employees and his dependents is all in one row when per dependents should be under the principal data.

Are all copied columns will go to Sheet2, right? - YES
if so, there will be inter lapping cells from Sheet1 to Sheet2? I don't know if what my observation is correct? - I am not sure again if that will cause an inter lapping as i wanted to just copy the dependents of the employee (principal) under their information in each row. Just like how it should be in Sheet2.

Though the code seems not working from my end. I tried to put the command button on both sheets (PAGE1 and PAGE02) so that to work the ActiveSheet function, but nothing happens upon clicking the button to execute the code.

 

[PreciousM]

Thank you for this Please see my answers on your below questions:

based on your Condition Sheet2 needs more columns as target from Sheet1 - I am not sure if i get this right, but all i wanted is to format the copied raw data from Sheet1 to Sheet2. As you can see in Sheet1, the details of employees and his dependents is all in one row when per dependents should be under the principal data.

Are all copied columns will go to Sheet2, right? - YES
if so, there will be inter lapping cells from Sheet1 to Sheet2? I don't know if what my observation is correct? - I am not sure again if that will cause an inter lapping as i wanted to just copy the dependents of the employee (principal) under their information in each row. Just like how it should be in Sheet2.

Though the code seems not working from my end. I tried to put the command button on both sheets (PAGE1 and PAGE02) so that to work the ActiveSheet function, but nothing happens upon clicking the button to execute the code.

I also tried to use record macro and below is the resulted code:

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("PAGE1").Select
    Range("B3:X3").Select
    Selection.Copy
    Sheets("PAGE02").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("PAGE1").Select
    Range("Y3").Select
    
    Range("B3:N3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("PAGE02").Select
    Range("A3").Select
    ActiveSheet.Paste
    
    Sheets("PAGE1").Select
    Range("Z3:AI3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("PAGE02").Select
    Range("N3").Select
    ActiveSheet.Paste
    
End Sub

With this, i tried to insert some of your codes to loop it in each row but it is not working.

 

[airforceone]

Hmmm what seems to be the problem?
anyway here's my settings for my Test Unit

1. MS Office Pro Plus 2019 (though I think It won't matter)
2. VBA Modules under "PERSONAL.XLSB"
   (kindly forgive me, but here's a link on how to create Personal Macro Workbook "PERSONAL.XLSB") Personal Macro Workbook if you have not created one.

You can make all your variables declared as public if you like (it's really up to you)
In my new sample I included a sheet for the button. (Just to demonstrate that it is working )
For the column copying issue disregard that for the mean time, after successfully running the code you may adjust the range for the source and target columns accordingly.
All code tested and working, update for any issue...

Option Explicit
Sub A_PROB102_CODE04_PRECIOUS()

    Dim FilteR As String, MyCaption As String, SourceFilename As String, xFileName As String, FNDirectory As String
    Dim WSCopy As Worksheet, SourceWBook As Workbook
    Dim LRow As Long, LCol As Long, HulingBanig As Long
    Dim Row4Sheet2 As Integer, iCTR As Integer

    Set WSCopy = Sheets("PAGE1")
    WSCopy.Activate
    'Count Row even with Empty Cells
    LRow = ActiveSheet.UsedRange.rows.Count + ActiveSheet.UsedRange.rows(1).Row

    iCTR = 2
    Row4Sheet2 = 2
    Do While iCTR < LRow
        If Not IsEmpty(Cells(iCTR, 2)) Then
            WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
            If UCase(Cells(iCTR, 25)) = "YES" Then
                WSCopy.Range("AA2" & iCTR & ":AI" & iCTR).Copy Worksheets("PAGE02").Range("K" & Row4Sheet2 & ":S" & Row4Sheet2)
            End If
            Row4Sheet2 = Row4Sheet2 + 1
        End If
        iCTR = iCTR + 1
    Loop
    
End Sub

Update Sample File PROB102 CODE05 PreciousM

 

[PreciousM]

Hi

Hmmm what seems to be the problem?
anyway here's my settings for my Test Unit

1. MS Office Pro Plus 2019 (though I think It won't matter)
2. VBA Modules under "PERSONAL.XLSB"
   (kindly forgive me, but here's a link on how to create Personal Macro Workbook "PERSONAL.XLSB") Personal Macro Workbook if you have not created one.

You can make all your variables declared as public if you like (it's really up to you)
In my new sample I included a sheet for the button. (Just to demonstrate that it is working )
For the column copying issue disregard that for the mean time, after successfully running the code you may adjust the range for the source and target columns accordingly.
All code tested and working, update for any issue...

Option Explicit
Sub A_PROB102_CODE04_PRECIOUS()

    Dim FilteR As String, MyCaption As String, SourceFilename As String, xFileName As String, FNDirectory As String
    Dim WSCopy As Worksheet, SourceWBook As Workbook
    Dim LRow As Long, LCol As Long, HulingBanig As Long
    Dim Row4Sheet2 As Integer, iCTR As Integer

    Set WSCopy = Sheets("PAGE1")
    WSCopy.Activate
    'Count Row even with Empty Cells
    LRow = ActiveSheet.UsedRange.rows.Count + ActiveSheet.UsedRange.rows(1).Row

    iCTR = 2
    Row4Sheet2 = 2
    Do While iCTR < LRow
        If Not IsEmpty(Cells(iCTR, 2)) Then
            WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
            If UCase(Cells(iCTR, 25)) = "YES" Then
                WSCopy.Range("AA2" & iCTR & ":AI" & iCTR).Copy Worksheets("PAGE02").Range("K" & Row4Sheet2 & ":S" & Row4Sheet2)
            End If
            Row4Sheet2 = Row4Sheet2 + 1
        End If
        iCTR = iCTR + 1
    Loop
   
End Sub

Update Sample File PROB102 CODE05 PreciousM

It is now working, thank you! First condition is working as it does not copy blanks rows.

Though for the second if condition, i tried to tweak your code a little to make the other dependent's (row 3, Y25 = Yes) details be pasted on the next row (under the principal data) but it seems not working as it should be.

Sub Copy()

    Dim FilteR As String, MyCaption As String, SourceFilename As String, xFileName As String, FNDirectory As String
    Dim WSCopy As Worksheet, SourceWBook As Workbook
    Dim LRow As Long, LCol As Long, HulingBanig As Long
    Dim Row4Sheet2 As Integer, iCTR As Integer

    Set WSCopy = Sheets("PAGE1")
    WSCopy.Activate
    'Count Row even with Empty Cells
    LRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row

    iCTR = 2
    Row4Sheet2 = 2
    Row4Sheet3 = Row4Sheet2 + 1
    Do While iCTR < LRow
    
        If Not IsEmpty(Cells(iCTR, 2)) Then
        WSCopy.Range("B" & iCTR & ":X" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
        
            If UCase(Cells(iCTR, 24)) = "Yes" Then
                WSCopy.Range("Z" & iCTR & ":AH" & iCTR).Copy Worksheets("PAGE02").Range("N" & Row4Sheet3 & ":W" & Row4Sheet3)
            End If
            Row4Sheet2 = Row4Sheet2 + 1
        
        End If
        iCTR = iCTR + 1
    Loop
    
End Sub

Attaching here the updated table i made.

Sheet - PAGE1

3232022 airforceone.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
1	Before you proceed to the start with the enrollment of your dependents, we need your acknowledgement that you have carefully read the FY22 Renewal of HMO Services deck and other relevant links to guide you with the entire process. All the links on your email will provide you information about: (a) enhancements in our benefits, (b) the retained annual premium rates to be co-shared with the company, (c) enrollment hierarchy to follow, (d) required supporting documents for new enrollments and the (e) Pre-Termination agreement. HMO Dependent enrollment/ membership is an extension of your benefit as an active and regular employee of the company. Cancellations due to separations will automatically deactivate the membership of your dependent/s' account. Refund is not applicable for separations after more than 6 months of enrollment. If you missed the deck, you may still read and access the link here: FY22 HMO Enrollment Program. Please select applicable response below:Â	Employee ID Number	Please enter your office email address to receive a copy of the enrollment form.	Last Name:Complete Name :Employee Name	Given Name:Complete Name :Employee Name	Middle Name:Complete Name :Employee Name	Middle Initial (M.I to use for card printing):Complete Name :Employee Name	Ext (Jr., II, etc.):Complete Name :Employee Name	Employee's Current Civil Status	Enrollment Classification(Enrollment classification will determine the available benefit plan options)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Enrollment Eligibilities:Enrollment Details	Last Name:Dependent 1:Dependent Information	Given Name:Dependent 1:Dependent Information	Middle Name:Dependent 1:Dependent Information	M.I. to Use:Dependent 1:Dependent Information	Ext. (Jr., II, etc.):Dependent 1:Dependent Information	Relationship:Enrollment Details	Date of Birth:Enrollment Details	Sex:Enrollment Details	Civil Status:Enrollment Details	Do you wish to enroll another dependent (2nd, in the hierarchy line)?:Enrollment Details	Enrollment Eligibilities:Enrollment Details	Last Name:Dependent 2:Dependent Information	Given Name:Dependent 2:Dependent Information	Middle Name:Dependent 2:Dependent Information	M.I. to Use:Dependent 2:Dependent Information	Ext. (Jr., II, etc.):Dependent 2:Dependent Information	Relationship:Enrollment Details	Date of Birth:Enrollment Details	Sex:Enrollment Details	Civil Status:Enrollment Details
2
3	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	1234	me1234@yahoo.com	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist			Regular Private | 250,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married	Yes	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Mia Fortuna	Angelesi	A		Sister	1/5/2004	Female	[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings
4
5
6
7
8	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	2468	elias6789@yahoo.com	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/2021	Female	Widow/Widower	No
9
10	Yes - I have read the deck and relevant links about the FY22 HMO Enrollment Program.	3690	yeahboy@yahoo.com	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00
PAGE1

Sheet PAGE02

3232022 airforceone.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Employee ID Number	Please enter your office email address to receive a copy of the enrollment form.	Last Name:Complete Name :Employee Name	Given Name:Complete Name :Employee Name	Middle Name:Complete Name :Employee Name	Middle Initial (M.I to use for card printing):Complete Name :Employee Name	Ext (Jr., II, etc.):Complete Name :Employee Name	Employee's Current Civil Status	Enrollment Classification(Enrollment classification will determine the available benefit plan options)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Choose a Benefit Plan for your dependents(Remember that you may choose an equal or lower benefit plan)	Enrollment Eligibilities:Enrollment Details	Last Name:Dependent 1:Dependent Information	Given Name:Dependent 1:Dependent Information	Middle Name:Dependent 1:Dependent Information	M.I. to Use:Dependent 1:Dependent Information	Ext. (Jr., II, etc.):Dependent 1:Dependent Information	Relationship:Enrollment Details	Date of Birth:Enrollment Details	Sex:Enrollment Details	Civil Status:Enrollment Details
2	1234	me1234@yahoo.com	Alivarez	Ranz	Angelesi	A		Single	Dependents of Supervisor | Specialist			Regular Private | 250,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Alivarez	Fazima	Angelesi	A		Mother	10/14/1966	Female	Married
3	2468	elias6789@yahoo.com	Nablos	Vincent	Guccena	G		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Nablos	Asunciona	Guccena	G		Mother	8/14/2021	Female	Widow/Widower
4	3690	yeahboy@yahoo.com	Jordan	Danvie	Lucena	B		Single	Dependents of Supervisor | Specialist			Regular Private | 150,000.00
5	1089	gandaghorl@yahoo.com	Maharlika	Dhannie	May	Z		Single	Dependents of Supervisor | Specialist			Regular Private | 350,000.00		[Immediate Dependent] Single, enrolling parent/s, child/ren and siblings	Maharlika	Seanti	Deanna	D		Father	4/25/1958	Male	Married
PAGE02

Hope you could still help me resolve the second condition and loop it through in each rows. Thank you!!!