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]

On second condition:
If Y2 = "Yes"
1. What Column to copy aside from Columns B:N of Sheet1 to Columns B:N of Sheet2
Set A Columns P:X to Sheet2 Columns AJ:AU or BF:BQ?
Set B Columns AA:AI to Sheet2 Columns AJ:AU or BF:BQ?

2. Do you need to copy it on the SAME or AFTER rows of the employee on Sheet2?

3. on Sheet2, what value does the following Columns need? (Maybe same with Number 1)
AJ:AU (Letter C on attached Image)
BF:BQ (Letter D on attached Image)
CB? (Letter E on attached Image)

 

[PreciousM]

On second condition:
If Y2 = "Yes"
1. What Column to copy aside from Columns B:N of Sheet1 to Columns B:N of Sheet2
Set A Columns P:X to Sheet2 Columns AJ:AU or BF:BQ?
Set B Columns AA:AI to Sheet2 Columns AJ:AU or BF:BQ?

2. Do you need to copy it on the SAME or AFTER rows of the employee on Sheet2?

3. on Sheet2, what value does the following Columns need? (Maybe same with Number 1)
AJ:AU (Letter C on attached Image)
BF:BQ (Letter D on attached Image)
CB? (Letter E on attached Image)

Hello, please see below details:

rows B:N of Sheet1 is the employee data. (should be in row2 of Sheet 2)
rows O:Y of Sheet1 is the start of employee's first dependent (should be in row2 of Sheet2 beside employee data being the first dependent)
rows Z:AH of Sheet1 is the second dependent of the same employee (should be in row 3 of Sheet2 after the row of employee being the second dependent)

If Y2 = "Yes" - Y2 of Sheet1
1. What Column to copy aside from Columns B:N of Sheet1 to Columns B:N of Sheet2
Set A Columns P:X to Sheet2 Columns AJ:AU or BF:BQ?
Set B Columns AA:AI to Sheet2 Columns AJ:AU or BF:BQ?
- based on the new worksheets i have shared so that to balance the number of columns of both sheets,
If Y2 of Sheet1 = "Yes" then
Copy rows B2:N2 of Sheet1 to rows A3:M3 of Sheet2 and rows Z2:AH2 of Sheet1 to rows N3:W3 of Sheet2.

But if Y2 of Sheet1 = "No" then
Copy the next employee data on the next row of Sheet1 to copy rows B3:X3 of sheet1 to rows A3:W3 of Sheet2.

Then check again column Y (this time Y3) if value is Yes or No then do the same as above

2. Do you need to copy it on the SAME or AFTER rows of the employee on Sheet2?
- it should copy after rows of the employee on Sheet2

3. on Sheet2, what value does the following Columns need? (Maybe same with Number 1)
AJ:AU (Letter C on attached Image) - it shouldn't have values as Sheet2 columns is only until W. If Sheet1 columns AJ:AU will have other dependents data of the employee, it should always be copy on the next row after the employee of Sheet2.
BF:BQ (Letter D on attached Image)
CB? (Letter E on attached Image)

i hope this will help. Sorry if i am struggling to explain it further from the start. But technically the employee data and their dependents is all in just one row of Sheet1. And what i needed to do is to have these dependents (starting to second dependent) to be below the employee (each row) and not just all in one row.

Thank you!

 

[airforceone]

Kindly see attached image.
Is the presentation of data correct? if so, what output is needed?

 

[airforceone]

you may want to test my updated code [TESTED]

Sub A_PROB102_CODE05_PreciousM()

    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       
            If UCase(Cells(iCTR, 25)) = "YES" Then
                'Copy Employee Record
                WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
                'Copy First Dependent of Employee
                WSCopy.Range("O" & iCTR & ":X" & iCTR).Copy Worksheets("PAGE02").Range("N" & Row4Sheet2)
                'Copy 2nd Dependent
                WSCopy.Range("Z" & iCTR & ":AH" & iCTR).Copy Worksheets("PAGE02").Range("N" & Row4Sheet2 + 1)
                'Advance two row if with 2nd Dependent
                Row4Sheet2 = Row4Sheet2 + 2
            Else
                WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
                WSCopy.Range("O" & iCTR & ":X" & iCTR).Copy Worksheets("PAGE02").Range("N" & Row4Sheet2)
                Row4Sheet2 = Row4Sheet2 + 1
            End If
        End If
        iCTR = iCTR + 1
    Loop   
End Sub

update us how it goes

 

[PreciousM]

Hi. Ideally the output should be like output1

Kindly see attached image.
Is the presentation of data correct? if so, what output is needed?

 

[PreciousM]

you may want to test my updated code [TESTED]

Sub A_PROB102_CODE05_PreciousM()

    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      
            If UCase(Cells(iCTR, 25)) = "YES" Then
                'Copy Employee Record
                WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
                'Copy First Dependent of Employee
                WSCopy.Range("O" & iCTR & ":X" & iCTR).Copy Worksheets("PAGE02").Range("N" & Row4Sheet2)
                'Copy 2nd Dependent
                WSCopy.Range("Z" & iCTR & ":AH" & iCTR).Copy Worksheets("PAGE02").Range("N" & Row4Sheet2 + 1)
                'Advance two row if with 2nd Dependent
                Row4Sheet2 = Row4Sheet2 + 2
            Else
                WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2)
                WSCopy.Range("O" & iCTR & ":X" & iCTR).Copy Worksheets("PAGE02").Range("N" & Row4Sheet2)
                Row4Sheet2 = Row4Sheet2 + 1
            End If
        End If
        iCTR = iCTR + 1
    Loop  
End Sub

update us how it goes

Yup, will try this and update you. Thanks so much!

 

[airforceone]

Hi. Ideally the output should be like output1

for output1 kindly insert

                'Copy Employee Record for SECOND DEPENDENT
                WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2 + 1)

see attached image

 

[PreciousM]

for output1 kindly insert

                'Copy Employee Record for SECOND DEPENDENT
                WSCopy.Range("B" & iCTR & ":N" & iCTR).Copy Worksheets("PAGE02").Range("A" & Row4Sheet2 + 1)

see attached image

Awesome! It is now working as expected really a BIG THANKS TO YOU!!! You're a savior ♡♡♡

 

[airforceone]

It took a while but we managed to at least do with what we know.... a welcome update...
keep posting, someone from the group might (will always) have a solution for you... ciao!