Copy specific rows from sheet to another when there is multiple sheets

[lunatu]

Hi, could some one help me with this...

Im having an Excel where there is one "Main" sheet where all the data is. Based on the value on column I specific cells (A, B, C, E, F, J, K L) are copied automatically into another sheet. There are multiple sheets (all named by employee name) and the macro should recognize in which sheet the rows should be copied based on "Main" sheets column J (employees name in this column, same as there is in sheets).

Is this possible to solve?

 

[Fluff]

What is the value in Col I that determines if the row should be copied?

 

[lunatu]

It is text "Sold" in column I so all the rows with status "Sold" should be copied into the specific employees sheet.

 

[Fluff]

Ok, how about

Sub lunatu()
   Dim Cl As Range
   Dim Dic As Object
   Dim i As Long
   
   Application.ScreenUpdating = False
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Main")
      For Each Cl In .Range("J2", .Range("J" & Rows.Count).End(xlUp))
         Dic(Cl.Value) = Empty
      Next Cl
      For i = 0 To Dic.Count - 1
         .Range("A1:L1").AutoFilter 9, "Sold"
         .Range("A1:N1").AutoFilter 10, Dic.Keys()(i)
         If Evaluate("isref('" & Dic.Keys()(i) & "'!A1)") Then
            Intersect(.AutoFilter.Range.Offset(1).EntireRow, .Range("A:C,E:F,J:L")).Copy Sheets(Dic.Keys()(i)).Range("F" & Rows.Count).End(xlUp).Offset(1, -5)
         End If
      Next i
      .AutoFilterMode = False
   End With
End Sub

 

[lunatu]

Great thanks!

 

[Fluff]

You're welcome & thanks for the feedback.