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



[TABLE="width: 1152"]
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Agent #[/TD]
[TD="width: 64"]Agency Name[/TD]
[TD="width: 64"]Agent First name[/TD]
[TD="width: 64"]Agent Last name[/TD]
[TD="width: 64"]Agency Location Address[/TD]
[TD="width: 64"]Agency Location City[/TD]
[TD="width: 64"]Agency Location Code[/TD]
[TD="width: 64"]Agency Location State[/TD]
[TD="width: 64"]Agency Location Zip Code[/TD]
[TD="width: 64"]Agency Termination Date[/TD]
[TD="width: 64"]Agency Status[/TD]
[TD="width: 64"]New Owner[/TD]
[TD="width: 64"]Sale Date[/TD]
[TD="width: 64"]Entry Date[/TD]
[TD="width: 64"]Sale Type[/TD]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Days[/TD]
[TD="width: 64"]Policy Count[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]NJ Region[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]NY Region[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]FL Region[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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/