I have tried to adjust a VBA Code that was built for me by @Fluff and I thought I was close to getting it to work, but I was mistaken.
Sub Kyosti()
Dim Cl As Range
Dim Ws As Worksheet
Dim Ky As Variant
Set Ws = Sheets("Data")
With CreateObject("scripting.dictionary")
For Each Cl In Ws.Range("O2", Ws.Range("O" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Empty
Next Cl
For Each Ky In .Keys
Ws.Range("A1:O1").AutoFilter 15, Ky
Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
Ws.AutoFilter.Range.SpecialCells(xlVisible).EntireRow.Copy Range("A1")
Next Ky
Ws.AutoFilterMode = False
End With
End Sub
The original code was written for a different file but I am attempting to use the same logic on the file outlined below
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>
The new logic would essentially create a new tab for every region with the filtered information. I made adjustments to the original code to match the new file, but I get a debug at:
Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
Can anyone help me understand the language or what it is doing so I can adjust accordingly!? Any help is greatly appreciated.
Sub Kyosti()
Dim Cl As Range
Dim Ws As Worksheet
Dim Ky As Variant
Set Ws = Sheets("Data")
With CreateObject("scripting.dictionary")
For Each Cl In Ws.Range("O2", Ws.Range("O" & Rows.Count).End(xlUp))
.Item(Cl.Value) = Empty
Next Cl
For Each Ky In .Keys
Ws.Range("A1:O1").AutoFilter 15, Ky
Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
Ws.AutoFilter.Range.SpecialCells(xlVisible).EntireRow.Copy Range("A1")
Next Ky
Ws.AutoFilterMode = False
End With
End Sub
The original code was written for a different file but I am attempting to use the same logic on the file outlined below
Agent # | Agency Name | Agent First name | Agent Last name | Agency Location Address | Agency Location City | Agency Location Code | Agency Location State | Agency Location Zip Code | Agency Termination Date | Agency Status | New Owner | Sale Date | Entry Date | Sale Type | Region | Days | Policy Count |
NJ Region | |||||||||||||||||
NY Region | |||||||||||||||||
FL Region |
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>
The new logic would essentially create a new tab for every region with the filtered information. I made adjustments to the original code to match the new file, but I get a debug at:
Sheets.Add(, Sheets(Sheets.Count)).Name = Ky
Can anyone help me understand the language or what it is doing so I can adjust accordingly!? Any help is greatly appreciated.