The below post has a discussion re duplicate rows being copied to new sheets.
http://www.mrexcel.com/forum/showthread.php?t=328460
The code works fine, until the data it takes to name new sheets exceeds 31 characters.
Is there VBA coding that in naming from this column -
"wsNew.Name = wsCrit.Range("A2")"
can be set to a maximum of 30 characters. If data in the A2 column exceeds this the vba will result in an error.
http://www.mrexcel.com/forum/showthread.php?t=328460
The code works fine, until the data it takes to name new sheets exceeds 31 characters.
Is there VBA coding that in naming from this column -
"wsNew.Name = wsCrit.Range("A2")"
can be set to a maximum of 30 characters. If data in the A2 column exceeds this the vba will result in an error.
Code:
Sub DistributeRows()
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(1) ' change 1 to the name of the worksheet the existing data is on
LastRow = wsAll.Range("A" & Rows.Count).End(xlUp).Row
Set wsCrit = Worksheets.Add
' column C has the criteria of Custody
wsAll.Range("C1:C" & LastRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wsCrit.Range("A1"), Unique:=True
LastRowCrit = wsCrit.Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRowCrit
Set wsNew = Worksheets.Add
wsNew.Name = wsCrit.Range("A2")
wsAll.Rows("1:" & LastRow).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=wsCrit.Range("A1:A2"), _
CopyToRange:=wsNew.Range("A1"), Unique:=False
wsCrit.Rows(2).Delete
Next I
Application.DisplayAlerts = False
wsCrit.Delete
Application.DisplayAlerts = True
End Sub