VBA Help

[Kyosti]

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

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.

 

[Kyosti]

So it seems that the error was being caused by N/A Values. Once I reconciled for that it works just fine. But it is not creating the tabs at the end or in Alphabetical order. I thought that was part of the code originally. Any suggestions??

 

[Logit]

.
"But it is not creating the tabs at the end or in Alphabetical order."

At the end of what ?

The tabs are created in the order found beginning on Row #2 and moving down. Arranging in alphabetical order requires additional code.


In your workbook ... how many tabs are being created ?

 

[Kyosti]

The file has 5 starting tabs, and the macro creates 14-15 new tabs. In my other code on the older file it is built in to create the new tabs at the end and in alphabetical order. I just thought the code I grabbed had that portion of the code included.

 

[Logit]

.
Ok ... better understanding.

The example provided does place the new tabs after all other tabs already in existence.

This link will give you example macros for alphabetizing the tabs.
https://www.ablebits.com/office-addins-blog/2018/05/02/alphabetize-tabs-excel/