create new sheet based on cell value and copy data

[Hasson]

hello

I have sheet MAIN contains data from row 4 . the range is (A4:H) and B2= name customer . so what I want if the B2 is Hasson ,then should create new sheet's name is Hasson and copy data from sheet MAIN into sheet Hasson from row2 and if I return to copy data for sheet has already created , then should copy to the bottom and if the name is new based on B2 then should create new sheet and copy the data from row2 and so on

 

[Hasson]

I know but based on your post#18 your result is different for my result , am I right or misunderstand?
the question is , why the borders don't include into the headers ?

 

[mumps]

The borders are actually there but it is hard to see them because of the color fill. This version of the macro makes the borders a little thicker.

rivate Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet
    Set srcWS = ActiveSheet
    If Not Evaluate("isref('" & Target.Value & "'!A1)") Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = Target
        With ActiveSheet
            .Range("A1").Resize(, 8).Value = Array("ITEM", "ID", "BRAND", "MANFACT", "REF", "BATCH", "ORDER", "QTY")
            srcWS.UsedRange.Offset(4).Copy .Range("A2")
            .UsedRange.Cells.WrapText = False
            .Columns.AutoFit
            .Range("A1").Interior.ColorIndex = 23
            .Range("B1").Resize(, 7).Interior.ColorIndex = 33
            With .UsedRange.Cells.Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
    Else
        With Sheets(Target.Value)
            .UsedRange.Offset(1).ClearContents
            ActiveSheet.UsedRange.Offset(4).Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
    End If
    Application.ScreenUpdating = True
End Sub

If they are still not visible, change:

.Weight = xlMedium

to

.Weight = xlThick

 

[Hasson]

magnificent ! thanks so much for your assistance

 

[mumps]

You are very welcome.