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.

 

[Fluff]

Is the region column O or column P?

 

[Logit]

Option Explicit


Sub Kyosti()
Dim Cl As Range
Dim Ws As Worksheet
Dim Ky As Variant


Set Ws = Sheets("Sheet1")


With CreateObject("scripting.dictionary")


    For Each Cl In Ws.Range("P2", Ws.Range("P" & Rows.Count).End(xlUp))
        .Item(Cl.Value) = Empty
    Next Cl
    
    For Each Ky In .Keys
        Ws.Range("A1:R1").AutoFilter 16, 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

 

[Kyosti]

It would be in P @Fluff

 

[Kyosti]

I swear I tried that code too, but I must have been off somewhere! Thank you so much @Logit, however I am still getting an error on the same line. But it did create half of the tabs.

 

[Logit]

.
The edits in the macro function as expected here. Something else going on with your workbook ?

Is there additional code were aren't aware of ?

 

[Kyosti]

Correction the new error is in this line

Ws.Range("A1:R1").AutoFilter 16, Ky

 

[Kyosti]

The file has several different macros, but they are not connected to each other.

 

[Logit]

.
The only thing I can suggest is to :

Quit Excel.

Shut down your computer.

Turn it back on.

Try the workbook again.


I have found here .... oftentimes when an error has appeared more than once on a workbook I am building, the above steps are the only means of
clearing the cache or memory. I've edited the code and know it is correct but still receiving errors. When re-booting computer the error goes away.

 

[Logit]

You can download my example here :

https://www.amazon.com/clouddrive/share/hv0PnCGyHi444ZLwTNHMwhJfhYkDw24JtWEjYdI6mNc