S
Sjakkie
How do i change the script below to go through the list and have the
duplicates searched aswell but only
allow the name to be used once.
so that if there is
lead 1
lead 2
lead 3
lead 4
lead 4
lead 5
lead 6
lead 4
That it just makes the Worksheets
lead 1
lead 2
lead 3
lead 4
lead 5
lead 6
Sub SplitDump()
Dim sh As Worksheet, s As String
Dim i As Long, iloc as Long
Dim c As Range
Dim strAddress As String
Dim test As Integer
strMain = ActiveSheet.Name
i = 2
For Each c In Range("a1:a60")
strAddress = c.Address
If Len(c.Value) = 0 Then
MsgBox ("Finished")
Exit Sub
End If
If InStr(1, c.Value, "Lead:") Then
s = Trim(Right(c, Len(c) - 5))
iloc = Instr(1,s,"/",vbTextcompare)
if iloc <> 0 then
s = Trim(Left(s,iloc-1))
end if
Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
sh.Name = s
i = 2
Else
c.Resize(1, 5).Copy sh.Cells(i, "A")
i = i + 1
End If
Next c
End Sub
duplicates searched aswell but only
allow the name to be used once.
so that if there is
lead 1
lead 2
lead 3
lead 4
lead 4
lead 5
lead 6
lead 4
That it just makes the Worksheets
lead 1
lead 2
lead 3
lead 4
lead 5
lead 6
Sub SplitDump()
Dim sh As Worksheet, s As String
Dim i As Long, iloc as Long
Dim c As Range
Dim strAddress As String
Dim test As Integer
strMain = ActiveSheet.Name
i = 2
For Each c In Range("a1:a60")
strAddress = c.Address
If Len(c.Value) = 0 Then
MsgBox ("Finished")
Exit Sub
End If
If InStr(1, c.Value, "Lead:") Then
s = Trim(Right(c, Len(c) - 5))
iloc = Instr(1,s,"/",vbTextcompare)
if iloc <> 0 then
s = Trim(Left(s,iloc-1))
end if
Set sh = Sheets.Add(After:=Sheets(Sheets.Count))
sh.Name = s
i = 2
Else
c.Resize(1, 5).Copy sh.Cells(i, "A")
i = i + 1
End If
Next c
End Sub