Splitting out data into different workbooks

[JayB0730]

Hello,

I have a spreadsheet that contains several columns similar to the below:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Report Date:[/TD]
[TD]6/22/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Category[/TD]
[TD]Sub Category[/TD]
[TD]Reg Code[/TD]
[TD]Dist Code[/TD]
[TD]Area Code[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jon[/TD]
[TD]Doe[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]11[/TD]
[TD]000[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]11[/TD]
[TD]001[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jack[/TD]
[TD]Doe[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]11[/TD]
[TD]001[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Mary[/TD]
[TD]Doe[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]11[/TD]
[TD]002[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jasper[/TD]
[TD]Test[/TD]
[TD]Fruit[/TD]
[TD]Apple[/TD]
[TD]11[/TD]
[TD]002[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Veronica[/TD]
[TD]Test[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]12[/TD]
[TD]000[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Angel[/TD]
[TD]Test[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]12[/TD]
[TD]001[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Jim[/TD]
[TD]Doe[/TD]
[TD]Fruit[/TD]
[TD]Orange[/TD]
[TD]12[/TD]
[TD]001[/TD]
[TD]01[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jess[/TD]
[TD]Grass[/TD]
[TD]Veggie[/TD]
[TD]Carrot[/TD]
[TD]13[/TD]
[TD]000[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]June[/TD]
[TD]High[/TD]
[TD]Veggie[/TD]
[TD]Carrot[/TD]
[TD]13[/TD]
[TD]001[/TD]
[TD]00[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Jen[/TD]
[TD]One[/TD]
[TD]Veggie[/TD]
[TD]Carrot[/TD]
[TD]13[/TD]
[TD]001[/TD]
[TD]01[/TD]
[/TR]
</tbody>[/TABLE]


What I would like to be able to do is the following:

1. Create different workbooks (This example would result in 3 workbooks) that contain the same data where Reg Code (2 characters always) is the same.
2. Each workbook would contain the rows of information where Reg Code is the same; however, I would NOT like to include any rows containing "00" in the Area Code field.
3. Lines 1 and 2 would be copied over each time.
4. Create each file name based on "[Category]_[Sub Category]_MMMYYYY.xlsx"

TIA!

 

[JLGWhiz]

First, I would like to thank you both very much for your help. I started with Fluff's code and received an error message on the following line:

[COLOR=#333333]Ws.UsedRange.SpecialCells(xlVisible).Copy Range("A1")[/COLOR]

"Run time error 1004. Application Defined or Object Defined error". Not sure what to do from here.

This would normally default to the ActiveSheet.

Ws.UsedRange.SpecialCells(xlVisible).Copy Range("A1")

But since it is inside the With statement for the Dicionary, it might need to have the Ws variable to qualify it.

Ws.UsedRange.SpecialCells(xlVisible).Copy Ws.Range("A1")

 

[JayB0730]

Thanks, really appreciate your help with this. So trying to understand the code, what does the "EMPTY" do every time you find a value that is not equal to 00?

If Cl.Value <> "00" Then Dic(CStr(Cl.Value)) = [COLOR=#ff0000]Empty[/COLOR]

 

[Fluff]

A Dictionary contains a Key & an Item. That line adds the value of the cell as the Key & leaves the Item empty

 

[JayB0730]

Ok, so I can confirm via the immediate window that the dictionary is working and the values that I need to Autofilter in column 25 is being stored... However, when I step through the code

 Ws.Range("A2:Y2").AutoFilter 25, Dic.keys

then everything in the filter get's unselected, and the filter results in hiding all data. That is why i am getting blank files.

The big mystery is how to get this line of code to work.

 

[Fluff]

What values do you have in that column & what values do you want to exclude?

 

[JayB0730]

Very similar to the sample i gave they are 2 digit codes. Here is a complete list:

01
02
03
04
05
06
21
26
07
08
27

Since you had already excluded "00" that will obviously not show up in the dictionary - which is what we want; When I filter first on the following first, everything is filtered correctly:

Ws.Range("A2:Y2").AutoFilter 23, Cl.Value

==> Column 23, right now has values "31" and "32". It filtered on 31 first, which is correct.

What happens next is interesting. When I go to filter column 25 based on this code:

Ws.Range("A2:Y2").AutoFilter 25, Dic.keys

==> it hides everything. What's interesting is when I go back to see the values in the filter for column 23, only "32" appears and is unchecked.

**NOTE, some of the codes in the dictionary (column 25) are shared between "31" and "32" in column 23 (e.g. col 23(31) col 25 (01); col 23(32) col 25 (01)

 

[Fluff]

Oops, missed a bit from the 2nd filter. It worked for me because there was only 1 value to filter on

Ws.Range("A2:Y2").AutoFilter 25, Dic.keys[COLOR=#ff0000], xlFilterValues[/COLOR]

 

[JayB0730]

Fantastic! It's always the simple things that break things, right?
It worked like a charm. Thank you very much for all of your patience and help with this code. I'm sure I'll be back with the next iteration of this project

Thanks again.
-J

 

[Fluff]

Glad to help & thanks for the feedback