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 Don't know if it helps but can you try to merge below code change appropriate variable, etc..

For Row2 Until Lastrow
    IF NOT ISNULL(sheet1.B2)
        sheet1.Range("B2:X2").Copy Worksheets("Sheet2").Range("A2")
    end if
Next

For Row2 Until Lastrow
    if "Y"
        sheet1.Range("B2:N2").Copy Worksheets("Sheet2").Range("AA2:AI2")
    end if
Next

 

[PreciousM]

Hi! thank you for sharing this to me. Really appreciate it. I actually dont have any idea on where i can insert this code to mine. Below is my current code to capture my first condition. However, it is not working and just keep on looping to execute the code. it wont stop once i click the command button.

Sub Copy2()

Sheets("Sheet1").Select

' Find the last row of data
SecondRow = Cells(Rows.Count, 1).End(xlDown).Row
' Loop through each row
For i = 2 To SecondRow

ThisValue = Cells(i, 2).Value

If ThisValue <> " " Then
Cells(i, 2).Resize(1, 23).Copy
Sheets("Sheet2").Activate
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 2).Select
ActiveSheet.Paste
Sheets("Sheet1").Select

Else
Cells(NextRow, 1).Select
Sheets("raw data").Select

End If

Next i
Application.ScreenUpdating = True

End Sub

Hope you can still help me! thanks!

I Don't know if it helps but can you try to merge below code change appropriate variable, etc..

For Row2 Until Lastrow
    IF NOT ISNULL(sheet1.B2)
        sheet1.Range("B2:X2").Copy Worksheets("Sheet2").Range("A2")
    end if
Next

For Row2 Until Lastrow
    if "Y"
        sheet1.Range("B2:N2").Copy Worksheets("Sheet2").Range("AA2:AI2")
    end if
Next

 

[airforceone]

(UNTESTED) kindly try this

Sub Copy2()
Sheets("Sheet1").Select
SecondRow = Cells(Rows.Count, 1).End(xlDown).Row
    For i = 2 To SecondRow
    ThisValue = Cells(i, 2).Value
        If ThisValue <> " " Then             
            sheet1.Range("B" & I & ":N" & I).Copy Worksheets("Sheet2").Range("A" & I)   
        End If
    Next i
Application.ScreenUpdating = True
End Sub

BTW, what do you want to get from this code?

Else
Cells(NextRow, 1).Select
Sheets("raw data").Select

 

[PreciousM]

(UNTESTED) kindly try this

Sub Copy2()
Sheets("Sheet1").Select
SecondRow = Cells(Rows.Count, 1).End(xlDown).Row
    For i = 2 To SecondRow
    ThisValue = Cells(i, 2).Value
        If ThisValue <> " " Then            
            sheet1.Range("B" & I & ":N" & I).Copy Worksheets("Sheet2").Range("A" & I)  
        End If
    Next i
Application.ScreenUpdating = True
End Sub

BTW, what do you want to get from this code?

Else
Cells(NextRow, 1).Select
Sheets("raw data").Select

Hi! Thank you so much again, youre really a big help

The untested code seems like working though i kind of modify it a little as below.

Sub Copy2()

Dim srcws As Worksheet
Dim destws As Worksheet

Set srcws = Worksheets("Sheet1")
Set destws = Worksheets("Sheet2")

srcws.Select
SecondRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To SecondRow
ThisValue = Cells(i, 2).Value
If ThisValue <> " " Then
srcws.Range("B" & i & ":N" & i).Copy Destination:=destws.Range("B" & i)
End If
Next i
Application.ScreenUpdating = True
End Sub

While for the code below, i want it to work that if "ThisValue" is blank or has no value, it shouldnt copy a blank row. Only those have values should be copied and pasted to Sheet2. Then the code will loop through each row.

Else
Cells(NextRow, 1).Select
Sheets("raw data").Select[/CODE]

I hope you could still help me with this. Thank you thank you!

 

[airforceone]

Hi! Thank you so much again, youre really a big help

The untested code seems like working though i kind of modify it a little as below.

Sub Copy2()

Dim srcws As Worksheet
Dim destws As Worksheet

Set srcws = Worksheets("Sheet1")
Set destws = Worksheets("Sheet2")

srcws.Select
SecondRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To SecondRow
ThisValue = Cells(i, 2).Value
If ThisValue <> " " Then
srcws.Range("B" & i & ":N" & i).Copy Destination:=destws.Range("B" & i)
End If
Next i
Application.ScreenUpdating = True
End Sub

While for the code below, i want it to work that if "ThisValue" is blank or has no value, it shouldnt copy a blank row. Only those have values should be copied and pasted to Sheet2. Then the code will loop through each row.

Else
Cells(NextRow, 1).Select
Sheets("raw data").Select[/CODE]

I hope you could still help me with this. Thank you thank you!

did you run the code already? I think the first "if" would do the work, it will skip all blank entry

 

[airforceone]

try the code below tested working

'-----------------------------
' Set 1
'-----------------------------
SecondRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To SecondRow
    If IsEmpty(Cells(i, 2).Value) Then
        rows(i).Delete
    End If   
    If not IsEmpty(Cells(i, 2).Value) Then
        srcws.Range("B" & i & ":N" & i).Copy Destination:=destws.Range("B" & i)
    End If   
Next i
'-----------------------------
'Set 2
'-----------------------------
SecondRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To SecondRow
    If not IsEmpty(Cells(i, 2).Value) Then
        srcws.Range("B" & i & ":N" & i).Copy Destination:=destws.Range("B" & i)
    End If   
Next i
'Delete empty row from sheet2
    Set WSCopy = Worksheets("XOX")
    WSCopy.Select
    For i = 2 To LastRow
        If IsEmpty(Cells(i, 1).Value) Then
            rows(i).Delete
        End If
    Next i

Set 1
Deletes empty row from SourceSheet (Sheet1), so it won't be copied to TargetSheet (Sheet2)

Set 2
If there's still a need for that row in the SourceSheet(Sheet1), this set will delete all copied empty from TargetSheet (Sheet2)

 

[airforceone]

my final code [TESTED]

        dim Row4Sheet2 as integer
        Set WSCopy = ActiveSheet
        ActiveSheet.Select
        'Find the last non-blank cell in row 1
        LastRow = Cells(rows.Count, 1).End(xlUp).Row
        iCTR = 2
        Row4Sheet2 = 2
        Do While iCTR <= LastRow
            If Not IsEmpty(Cells(iCTR, 2)) Then
                WSCopy.Range("A" & iCTR & ":M" & iCTR).Copy Worksheets("XOX").Range("A" & Row4Sheet2)
                Row4Sheet2 = Row4Sheet2 + 1
            End If
            iCTR = iCTR + 1
        Loop

 

[PreciousM]

my final code [TESTED]

        dim Row4Sheet2 as integer
        Set WSCopy = ActiveSheet
        ActiveSheet.Select
        'Find the last non-blank cell in row 1
        LastRow = Cells(rows.Count, 1).End(xlUp).Row
        iCTR = 2
        Row4Sheet2 = 2
        Do While iCTR <= LastRow
            If Not IsEmpty(Cells(iCTR, 2)) Then
                WSCopy.Range("A" & iCTR & ":M" & iCTR).Copy Worksheets("XOX").Range("A" & Row4Sheet2)
                Row4Sheet2 = Row4Sheet2 + 1
            End If
            iCTR = iCTR + 1
        Loop

Hi airforceone! Really thank you for these codes. Such a big help super. Though i tried to check and run your 'final code', but upon clicking the button it was doing nothing. Didn't copy anything from sheet 1 to sheet2. Though i hope you could still help me with below conditions that i really needed to happen:

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 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.

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

 

[airforceone]

Hi airforceone! Really thank you for these codes. Such a big help super. Though i tried to check and run your 'final code', but upon clicking the button it was doing nothing. Didn't copy anything from sheet 1 to sheet2. Though i hope you could still help me with below conditions that i really needed to happen:

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 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.

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

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)