Need Macro Codes to Copy Columns Data to multiple worksheets if name matches

[Numaan Mujawar]

I have data in each cell below
Column A Column B Column C continues till Column J.

In 1 Column B i have Salesman Names (Ravi, Rinto, Abhishek, Vaseem and Naleem).

I have made spreadsheets for each salesman and i have summary sheet in which i will be posting data regularly, so as soon as i post data for any salesman in summary sheets then all column data should be copied to the respective Salesman sheet also.

Please advise me on this urgent.

 

[lrobbo314]

Try this. It assumes that your worksheets with the names on them already at least have the header data. Also, it assumes that your data being copied over is on a sheet called 'Main'. You my need to adjust that bit of code to whatever your sheet name is actually called.

Sub CopyOver()
Application.ScreenUpdating = False


Dim ws As Worksheet:    Set ws = Sheets("Main")
Dim r As Range:         Set r = ws.Range("A1:J" & ws.Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Columns(2).Value
Dim AL As Object:       Set AL = CreateObject("System.Collections.ArrayList")


For i = LBound(AR) + 1 To UBound(AR)
    If Not AL.contains(AR(i, 1)) Then AL.Add AR(i, 1)
Next i


With r
    For j = 0 To AL.Count - 1
        .AutoFilter 2, AL(j), xlFilterValues
        .Offset(1, 0).Resize(.Rows.Count - 1).Copy Sheets(AL(j)).Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilter
    Next j
End With


Application.ScreenUpdating = True
End Sub