Advanced Filter Copy to Another Location - Missing Data

[gquest]

Hello,

I'm having an issue with the advanced filter copy to another location function in excel. I have a spreadsheet that has 38 columns and 6,903 rows of data (including two header rows). I wrote a macro to filter and create tabs for all the unique records in column AL. The macro appears to work fine at first: pulls in the correct number of rows, pulls in the first few columns correctly, and even the last few correctly. The issue is the columns in the middle. They aren't getting copied. I tried doing the process manually, without the macro, and I'm having the same issue. Any thoughts? Is there a limit on data (columns and rows)? My code is below.

Thank you in advance for your help!

George

Option Explicit
Sub DistributeRows()
'this macro creates new sheets based on records in column
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim wsheet As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long

Application.ScreenUpdating = False

    Set wsAll = Worksheets("All")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row - 1
    Set wsCrit = Worksheets.Add
    
    wsAll.Range("al2:al" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row - 1
    For I = 2 To LastRowCrit
    
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        Set wsNew = Sheets("Template (2)")
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("2:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A3"), Unique:=False
        wsCrit.Rows(2).delete
        
    Next I
    
    Application.DisplayAlerts = False
    wsCrit.delete
    Application.DisplayAlerts = True
    
For Each wsheet In Sheets
Select Case wsheet.Name
Case "All", "Template"
'Do Nothing
Case Else
wsheet.Rows(3).delete
End Select
Next wsheet
    
    Application.ScreenUpdating = True
    
End Sub

 

[gquest]

Hello,

I'm having an issue with the advanced filter copy to another location function in excel. I have a spreadsheet that has 38 columns and 6,903 rows of data (including two header rows). I wrote a macro to filter and create tabs for all the unique records in column AL. The macro appears to work fine at first: pulls in the correct number of rows, pulls in the first few columns correctly, and even the last few correctly. The issue is the columns in the middle. They aren't getting copied. I tried doing the process manually, without the macro, and I'm having the same issue. Any thoughts? Is there a limit on data (columns and rows)? My code is below.

Thank you in advance for your help!

George

Option Explicit
Sub DistributeRows()
'this macro creates new sheets based on records in column
Dim wsAll As Worksheet
Dim wsCrit As Worksheet
Dim wsNew As Worksheet
Dim wsheet As Worksheet
Dim LastRow As Long
Dim LastRowCrit As Long
Dim I As Long

Application.ScreenUpdating = False

    Set wsAll = Worksheets("All")
    LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row - 1
    Set wsCrit = Worksheets.Add
    
    wsAll.Range("al2:al" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
    
    LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row - 1
    For I = 2 To LastRowCrit
    
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        Set wsNew = Sheets("Template (2)")
        wsNew.Name = wsCrit.Range("A2")
        wsAll.Rows("2:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
         CopyToRange:=wsNew.Range("A3"), Unique:=False
        wsCrit.Rows(2).delete
        
    Next I
    
    Application.DisplayAlerts = False
    wsCrit.delete
    Application.DisplayAlerts = True
    
For Each wsheet In Sheets
Select Case wsheet.Name
Case "All", "Template"
'Do Nothing
Case Else
wsheet.Rows(3).delete
End Select
Next wsheet
    
    Application.ScreenUpdating = True
    
End Sub

Opps I found the issue. I'm using a two header row system, so the column headers weren't unique were coming up blank.

Please delete this post.