error when copying duplicaties

[flunzy]

Hi guys,

I hope that you can help me with this one. I had a code that copied every row into a new sheet. This worked perfect. Now I wanted to copy all uniques and duplicates into a new sheet but I get an "for without next" error (see the code below). Does anyone have a suggestion because I can't see the cause:

        

Option Explicit

Sub SplitDataNrows()
Dim n As Long, rw As Long, LR As Long, Titles As Boolean
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long

Set wsAll = Worksheets("split")
    If MsgBox("Do you want to split?", vbYesNo, _
                "Split") = vbNo Then Exit Sub

    Application.ScreenUpdating = False
    With ActiveSheet
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        
        For rw = 2 + ---Titles To LR
            Sheets("testletter").Select
            Sheets("testletter").Copy After:=Sheets(3)
                LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
                .Rows(1).Copy Range("A1")
                wsAll.Range("A2:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
                LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
                For I = 2 To LastRowCrit
                
                wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A1"), Unique:=False
Next I
        
        .Activate
    Application.ScreenUpdating = True

End Sub

[MarvinP]

You have two For statements and only one Next statement

For rw = 2 + ---Titles To LR
For I = 2 To LastRowCrit
Next I

You need a
Next rw

somewhere in your code.

[flunzy]

You have two For statements and only one Next statement

For rw = 2 + ---Titles To LR
For I = 2 To LastRowCrit
Next I

You need a
Next rw

somewhere in your code.

Hi Marvin,

Thanks for the help. While you where responding, I have made up the code beneath. The error doesn't shows up but the macro doesn't does the thing that he has to do. In stead of grouping all numbers and place them into seperate sheets, it copies all data and pastes them in every sheet.

Option Explicit

Sub SplitDataNrows()
Dim n As Long, rw As Long, LR As Long, Titles As Boolean
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long

Set wsAll = Worksheets("split")
    If MsgBox("Do you want to split?", ?", vbYesNo, _
                "Split") = vbNo Then Exit Sub

    Application.ScreenUpdating = False
    With ActiveSheet
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        
        For rw = 2 + ---Titles To LR
            Sheets("testletter").Select
            Sheets("testletter").Copy After:=Sheets(3)
                'Kopieert gegevens van hoofdsheet naar brief
                LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
                .Rows(1).Copy Range("A1")
                wsAll.Range("A2:A" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("testletter").Range("A2"), Unique:=True
    
                LastRowCrit = Sheets("testletter").Range("A" & Rows.Count).End(xlUp).Row
                For I = 2 To LastRowCrit
                
                wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("testletter").Range("A1:A2"), _
         CopyToRange:=Sheets("testletter").Range("A1"), Unique:=False
               
        Next I
    Next rw
    End With
      
    Application.ScreenUpdating = True

End Sub

[MarvinP]

Hi,

You will need to show me what you are trying to achieve. Attaching a workbook with a before and after example will help me understand the question.

To attach a sample workbook, click on "Go Advanced" and then the Paper Clip Icon above the message area..

[flunzy]

Hi,

You will need to show me what you are trying to achieve. Attaching a workbook with a before and after example will help me understand the question.

To attach a sample workbook, click on "Go Advanced" and then the Paper Clip Icon above the message area..

Hi Marvin,

Thanks for the offer. hereby the requested file (before and after)

[MarvinP]

Hi Flunzy,

I hate giving answers that lead to harder problems instead of making them easier. You are asking to take a table (that I like) and split it onto multiple sheets (that I don't like).

Instead of writing VBA code to do this, I'd like you to look at Auto Filters on a single sheet. You would then filter the data and show exactly what you want on your individual sheets. See the attached with 3 examples. Two are Pivot tables that I don't think you want and the third is an AuotFilter of your data. Simply click on the drop down column names and filter your data. This will get what you want using a single table and it much more useful than having your data on multiple tables. AND you don't need any VBA...