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

PreciousM

New Member
Joined
Mar 9, 2022
Messages
14
Office Version
  1. 2016
Platform
  1. Mobile
  2. Web
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
ABCDEFGHIJKLMNOPQRS
1Employee NumberEmployee Last NameEmployee Given NameEmployee Middle NameEmployee M.IEmployee Ext (Jr., II, etc.)Employee Civil StatusEnrollment ClassificationBenefit PlanEnrollment EligibilitiesDependent Last NameDependent Given NameDependent Middle NameDependent M.IDependent Ext. (Jr., II, etc.)RelationshipDependent Date of BirthDependent SexDependent Civil Status
21234AlivarezRanzAngelesiASingleDependents of Supervisor | SpecialistRegular Private | P 250,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsAlivarezFazimaAngelesiAMother10/14/1966FemaleMarried
31234AlivarezRanzAngelesiASingleDependents of Supervisor | SpecialistRegular Private | P 250,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsAlivarezMia FortunaAngelesiASister1/5/2004FemaleSingle
42468NablosVincentGuccenaGSingleDependents of Supervisor | SpecialistRegular Private | P 150,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsNablosAsuncionaGuccenaGMother8/14/1969FemaleWidow/Widower
53690JordanDanvieLucenaBSingleDependents of Supervisor | SpecialistRegular Private | 150,000.00
61089MaharlikaDhannie EmoMayZSingleDependents of Expert | ManagerRegular Private | 450,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaSeantiDeannaDFather4/25/1958MaleMarried
71089MaharlikaDhannie EmoMayZSingleDependents of Expert | ManagerRegular Private | 450,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaDharling ErikaMayZSister5/4/2013FemaleSingle
81089MaharlikaDhannie EmoMayZSingleDependents of Expert | ManagerRegular Private | 450,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaDhannica MarieMayZSister5/4/2006FemaleSingle
enrollment report format


sample report.xlsx
ABCDEFGHIJKLMNOPQRS
1Employee NumberEmployee Last NameEmployee Given NameEmployee Middle NameEmployee M.IEmployee Ext (Jr., II, etc.)Employee Civil StatusEnrollment ClassificationBenefit PlanEnrollment EligibilitiesDependent Last NameDependent Given NameDependent Middle NameDependent M.IDependent Ext. (Jr., II, etc.)RelationshipDependent Date of BirthDependent SexDependent Civil Status
21234AlivarezRanzAngelesiASingleDependents of Supervisor | SpecialistRegular Private | P 250,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsAlivarezFazimaAngelesiAMother10/14/1966FemaleMarried
31234AlivarezRanzAngelesiASingleDependents of Supervisor | SpecialistRegular Private | P 250,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsAlivarezMia FortunaAngelesiASister1/5/2004FemaleSingle
42468NablosVincentGuccenaGSingleDependents of Supervisor | SpecialistRegular Private | P 150,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsNablosAsuncionaGuccenaGMother8/14/1969FemaleWidow/Widower
53690JordanDanvieLucenaBSingleDependents of Supervisor | SpecialistRegular Private | 150,000.00
61089MaharlikaDhannie EmoMayZSingleDependents of Expert | ManagerRegular Private | 450,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaSeantiDeannaDFather4/25/1958MaleMarried
71089MaharlikaDhannie EmoMayZSingleDependents of Expert | ManagerRegular Private | 450,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaDharling ErikaMayZSister5/4/2013FemaleSingle
81089MaharlikaDhannie EmoMayZSingleDependents of Expert | ManagerRegular Private | 450,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaDhannica MarieMayZSister5/4/2006FemaleSingle
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 NumberBefore 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 NumberBefore 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 NumberBefore 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 NumberBefore 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 DetailsLast Name:Dependent 1:Dependent InformationGiven Name:Dependent 1:Dependent InformationMiddle Name:Dependent 1:Dependent InformationM.I. to Use:Dependent 1:Dependent InformationExt. (Jr., II, etc.):Dependent 1:Dependent InformationRelationship:Enrollment DetailsDate of Birth:Enrollment DetailsSex:Enrollment DetailsCivil Status:Enrollment DetailsDo you wish to enroll another dependent (2nd, in the hierarchy line)?:Enrollment DetailsEnrollment Eligibilities:Enrollment DetailsLast Name:Dependent 2:Dependent InformationGiven Name:Dependent 2:Dependent InformationMiddle Name:Dependent 2:Dependent InformationM.I. to Use:Dependent 2:Dependent InformationExt. (Jr., II, etc.):Dependent 2:Dependent InformationRelationship:Enrollment DetailsDate of Birth:Enrollment DetailsSex:Enrollment DetailsCivil Status:Enrollment DetailsDo you wish to enroll another dependent (3rd, in the hierarchy line)?:Enrollment DetailsEnrollment Eligibilities:Enrollment DetailsLast Name:Dependent 3:Dependent InformationGiven Name:Dependent 3:Dependent InformationMiddle Name:Dependent 3:Dependent InformationM.I. to Use:Dependent 3:Dependent InformationExt. (Jr., II, etc.):Dependent 3:Dependent InformationRelationship:Enrollment DetailsDate of Birth:Enrollment DetailsSex:Enrollment DetailsCivil Status:Enrollment DetailsDo you wish to enroll another dependent (4th, in the hierarchy line)?:Enrollment DetailsEnrollment Eligibilities:Enrollment DetailsLast Name:Dependent 4:Dependent InformationGiven Name:Dependent 4:Dependent InformationMiddle Name:Dependent 4:Dependent InformationM.I. to Use:Dependent 4:Dependent InformationExt. (Jr., II, etc.):Dependent 4:Dependent InformationRelationship:Enrollment DetailsDate of Birth:Enrollment DetailsSex:Enrollment DetailsCivil Status:Enrollment DetailsDo you wish to enroll another dependent (5th, in the hierarchy line)?:Enrollment DetailsEnrollment Eligibilities:Enrollment DetailsLast Name:Dependent 5:Dependent InformationGiven Name:Dependent 5:Dependent InformationMiddle Name:Dependent 5:Dependent InformationM.I. to Use:Dependent 5:Dependent InformationExt. (Jr., II, etc.):Dependent 5:Dependent InformationRelationship:Enrollment DetailsDate of Birth:Enrollment DetailsSex:Enrollment DetailsCivil Status:Enrollment DetailsDo you wish to enroll another dependent (6th, in the hierarchy line)?:Enrollment DetailsEnrollment Eligibilities:Enrollment DetailsLast Name:Dependent 6:Dependent InformationGiven Name:Dependent 6:Dependent InformationMiddle Name:Dependent 6:Dependent InformationM.I. to Use:Dependent 6:Dependent InformationExt. (Jr., II, etc.):Dependent 6:Dependent InformationRelationship:Enrollment DetailsDate of Birth:Enrollment DetailsSex:Enrollment DetailsCivil Status:Enrollment DetailsDo 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 | SpecialistRegular Private | 250,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsAlivarezFazimaAngelesiAMother
10/14/1966​
FemaleMarriedYes[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsAlivarezMia FortunaAngelesiASister
1/5/2004​
FemaleSingleNo
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 | SpecialistRegular Private | 150,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsNablosAsuncionaGuccenaGMother
8/14/2021​
FemaleWidow/WidowerNo
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 | SpecialistRegular 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 | SpecialistRegular Private | 350,000.00[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaSeantiDeannaDFather
4/25/1958​
MaleMarriedYes[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaDharling ErikaMayZSister
5/4/2013​
FemaleSingleYes[Immediate Dependent] Single, enrolling parent/s, child/ren and siblingsMaharlikaDhannica MarieMayZSister
5/4/2006​
FemaleSingleNo
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I Don't know if it helps but can you try to merge below code change appropriate variable, etc..

VBA Code:
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
 
Upvote 0
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..

VBA Code:
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
 
Upvote 0
(UNTESTED) kindly try this
VBA Code:
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?

Code:
Else
Cells(NextRow, 1).Select
Sheets("raw data").Select
 
Upvote 0
(UNTESTED) kindly try this
VBA Code:
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?

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!
 
Upvote 0
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
 
Upvote 0
try the code below tested working
Code:
'-----------------------------
' 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)
 
Upvote 0
my final code [TESTED]

VBA Code:
        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
 
Upvote 0
my final code [TESTED]

VBA Code:
        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!
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top