copy data to another sheet

[reza_doang]

hi all,

hope someone can help me provide a macro to save my time :D
please find attached file for preview my data.
in sheet "Data" i have some of data which want to copy to sheet "Matrics".
In sheet Matrics in A2 i have Important - Not Urgent, I2 (Important - Urgent), A30 (Not Urgent - Not Important), I30 (Urgent - Not Important).

now in sheet Data if they meet criteria which is Importance + Urgency = A2/I2/A30/I30 then copy Category cell to below each criteria in sheet Matrics.

please see attached file for the examples.

Thanks

[mohanvijay]

Try this


Dim WS_Data As Worksheet, WS_Result As Worksheet
Dim L_Rw As Long
Dim ii As Byte, jj As Byte
Dim C_Im(1) As String
Dim C_Ur(1) As String
Dim T_Lng As Long
Dim Ctr As Integer
C_Im(0) = "Important"
C_Im(1) = "Not Important"
C_Ur(0) = "Urgent"
C_Ur(1) = "Not Urgent"
Set WS_Data = ThisWorkbook.Sheets("Data")
Set WS_Result = ThisWorkbook.Worksheets.Add
With WS_Data

If .FilterMode = True Then .ShowAllData

L_Rw = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("g1:h1").Value = .Range("e1:f1").Value

For ii = 0 To 1
For jj = 0 To 1
Ctr = Ctr + 1
.Range("g2").Value = C_Im(ii)
.Range("h2").Value = C_Ur(jj)

.Range("A1:F" & L_Rw).AdvancedFilter xlFilterInPlace, .Range("G1:H2")

T_Lng = .Cells(Rows.Count, 1).End(xlUp).Row
WS_Result.Cells(1, Ctr).Value = C_Im(ii) & " - " & C_Ur(jj)

If T_Lng > 1 Then

.Range("b2:b" & T_Lng).SpecialCells(xlCellTypeVisible).Copy WS_Result.Cells(2, Ctr)
End If



Next jj
Next ii

.ShowAllData
.Range("g1:h2").ClearContents

End With

Set WS_Data = Nothing
Set WS_Result = Nothing

[reza_doang]

hi mohanvijay,

Thanks for your response. basically the vba work but didn't like with i want to achieve.
in your code, after run the macro it will create a new worksheet, meanwhile i want to copy to sheet Matrics which for criteria;
Important - Not Urgent will fill into Cell A2:A27
Important - Urgent will fill into I2:I27
Not Urgent - Not Important will fill into A30:A50
Urgent - Not Important will fill into I30:I50

hope you or someone can modify the codes...

anyway many thanks for your help

regards,
Reza