Conditionally Copy and Paste of the data

Sachin Singh
Sachin Singh used Ask the Experts™
on
If Column H is greater than Column I and Column K is greater than Column D then paste the data to Sheet3
If Column H is lower than Column I and Column K is lower than Column D then paste the data to Sheet4
Book1.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please give this a try...
Sub ConditionalCopy()
Dim wsData As Worksheet, ws3 As Worksheet, ws4 As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
Set wsData = Sheets("Sheet2")
Set ws3 = Sheets("Sheet3")
Set ws4 = Sheets("Sheet4")
ws3.Cells.Clear
ws4.Cells.Clear
lr = wsData.Cells(Rows.Count, 1).End(xlUp).Row
wsData.Columns(1).Insert
wsData.Range("A2:A" & lr).Formula = "=IF(AND(I2>J2,L2>E2),TRUE,IF(AND(I2<J2,L2<E2),FALSE,""""))"
With wsData.Range("A1").CurrentRegion
    .AutoFilter field:=1, Criteria1:=True
    .SpecialCells(xlCellTypeVisible).Copy ws3.Range("A1")
    ws3.Columns(1).Delete
    .AutoFilter field:=1, Criteria1:=False
    .SpecialCells(xlCellTypeVisible).Copy ws4.Range("A1")
    ws4.Columns(1).Delete
End With
wsData.Columns(1).Delete
wsData.AutoFilterMode = False
Application.ScreenUpdating = True
MsgBox "Data has been copied successfully.", vbInformation
End Sub

Open in new window

Click the button called "Copy Data" on Sheet2 to run the code.
Sachin.xlsm

Author

Commented:
Thnx Neeraj Sir for ur great Support
Problem Solved
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Sachin!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial