Macro to copy based on a multiple conditions

[srinidhi]

Hi All,


I need macro to copy & paste data from one sheet to another. The macro should copy the data based on the following conditions:

Column B has NEW EMP & Old EMP,

Column C has Month,

Column H has list of clients

if NEW EMP is there in Column B & month is equal to current month, if both these conditions are stratified then it should look for Column H with specific names like ABC or DEG or IJK.


Then it should paste the entire column in sheet 8.

I was able to get just ½ way through: my code is


Sub Copypaste()

Set i = Sheets("Sheet1")

Set e = Sheets("Sheet8")

Dim d

Dim j

Set d = 1

Set j = 2

Do Until IsEmpty(i.Range("B" & j))


If i.Range("B" & j) = "New EMP" And i.Range("C" & j) = "May" Then

d = d + 1

e.Rows(d).Value = i.Rows(j).Value


End If

j = j + 1

Loop

End Sub


How to include the or condition in the above macro like if Column H = ABC or DEG or IJK.

 

[SirJB7]

Hi, srinidhi!


Is this what you're asking for?

-----

[pre]

Sub Copypaste()
Set i = Sheets("Sheet1")
Set e = Sheets("Sheet8")
Dim d
Dim j
Set d = 1
Set j = 2
Do Until IsEmpty(i.Range("B" & j))
If i.Range("B" & j) = "New EMP" And i.Range("C" & j) = "May" Then
If i.Range("H" & j) = "ABC" Or i.Range("H" & j) = "DEG" Or i.Range("H" & j) = "IJK" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value
End If
End If
j = j + 1
Loop
End Sub

[/pre]
-----


Regards!

 

[Debraj]

Hi Srinidi,


A Sample file is better in this situation..


By the way.. do you want "Column H = ABC or DEG or IJK. " with including previous conditions also..


can you please try the below MACRO..

Just sample use of AutoFilter.. with various techniques.

[pre]

Sub testMacro()
Sheets("Sheet8").Cells.Clear
With Sheets("Sheet1").Cells(1, 1).CurrentRegion
.AutoFilter 2, Criteria1:="New EMP", Operator:=xlOr, Criteria2:="OLD ERP"
.AutoFilter 3, Criteria1:="May"
.AutoFilter 8, Criteria1:=Array("Abc", "Deg", "IJL"), Operator:=xlFilterValues
.SpecialCells(12).Copy Sheets("Sheet8").[a1]
.AutoFilter
End With
End Sub

[/pre]

Regards,

Deb