First line missing in auto filter to multiple sheets

Something went wrong when attaching the workbook.

Artik

Thanks for the heads-up. Guess I should have checked but it was late lol

Now attached to OP

According to your attachment a starter VBA demonstration to paste only to the Sheet1 (Report) worksheet module :

PHP Code: 

Sub Demo1()
        Dim R&, V
    With Application
       .DisplayAlerts = False
       .ScreenUpdating = False
        For R = Worksheets.Count To Index + 1 Step -1:  Worksheets(R).Delete:  Next
    With [A5].CurrentRegion.Columns
       .Item(9).AdvancedFilter 2, , [R1], True
        [R1].CurrentRegion.Sort [R1], 1, Header:=1
        V = [R1].CurrentRegion
    For R = 2 To UBound(V)
        Worksheets.Add(, Worksheets(Worksheets.Count)).Name = V(R, 1)
        [R2] = V(R, 1)
       .AdvancedFilter 1, [R1:R2]
        UsedRange.Resize(, .Count).Copy ActiveSheet.[A1]
    Next
    End With
        ShowAllData
        [Q1].CurrentRegion.Clear
       .DisplayAlerts = True
       .ScreenUpdating = True
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by ~TaC~

I have a feeling this is something to do with the autofilter being addressed to the first row

The reason why your .Offset(1) has no sense in the Copy codeline where .SpecialCells is useless …

Originally Posted by Marc L

According to your attachment a starter VBA demonstration to paste only to the Sheet1 (Report) worksheet module :

PHP Code: 

Sub Demo1()
        Dim R&, V
    With Application
       .DisplayAlerts = False
       .ScreenUpdating = False
        For R = Worksheets.Count To Index + 1 Step -1:  Worksheets(R).Delete:  Next
    With [A5].CurrentRegion.Columns
       .Item(9).AdvancedFilter 2, , [R1], True
        [R1].CurrentRegion.Sort [R1], 1, Header:=1
        V = [R1].CurrentRegion
    For R = 2 To UBound(V)
        Worksheets.Add(, Worksheets(Worksheets.Count)).Name = V(R, 1)
        [R2] = V(R, 1)
       .AdvancedFilter 1, [R1:R2]
        UsedRange.Resize(, .Count).Copy ActiveSheet.[A1]
    Next
    End With
        ShowAllData
        [Q1].CurrentRegion.Clear
       .DisplayAlerts = True
       .ScreenUpdating = True
    End With
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thanks for reply, when run it comes up with Compile Error @ ShowAllData

Originally Posted by Marc L

The reason why your .Offset(1) has no sense in the Copy codeline where .SpecialCells is useless …

Thanks for reply, tried without Offset(1) and now sheet (1) has correct rows but all other sheets have an extra row

Originally Posted by ~TaC~

when run it comes up with Compile Error @ ShowAllData

Your bad as you felt into the bad reader trap ! So delete the code module
then well read the dark red direction before the code …

Originally Posted by ~TaC~

tried without Offset(1) and now sheet (1) has correct rows but all other sheets have an extra row

So keep the Offset but delete the useless SpecialCells and start your range from row #5 rather than 6 …

So not being the greatest with VBA it took me awhile to work this out based on your reply @Marc L, but I wanted to do it without asking repeatedly so it would help me understand it better.

It looks like I have it working with your suggestions Marc

Please Login or Register  to view this content.

.Range(.Cells(6, 9) - is data to look up
With .Range("A5:L" & .Cells - filters from header row
.Offset(1).Copy Sheets(ws).Range("A6") - copies to all sheets cell A6

Correct me if I'm wrong but all working good

Originally Posted by ~TaC~

.Range(.Cells(6, 9) - is data to look up

As it must start again from row #5 like you can check in VBA help and within my VBA demonstration …