Separating Sheet into different sheets based on cell "Containing" string in another sheet...

[rjbur]

Here is my challenge and I am continuing to search the forum for an example, so if there is another useful thread, please point me in that direction.

I have a spreadsheet/workbook that contains two (2) sheets. Lets call them "CRITERIA" and "DATA". I need to perpetrate the DATA into NEW sheets based on the CRITERIA and if the sheet does not exist, create it based on the Header Row in the CRITERIA sheet. If it does exist, then simply add the data to the end of the existing content in the sheet.

DATA contains a long list of... you guessed it, data. Column-A contains "text" or a string. For my example, anything past Column A is along for the ride.

Here is what the CRITERIA sheet might look like:

	A	B
1	CARS	COLOR
2	Dodge	Red
3	Chevy	Green
4	GMC	Blue

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Now the DATA sheet contains say something like:

A B C 1 Theredrugwill… Row 1 data More Row 1 data 2 Chevycarsare… Row 2 data More Row 2 data 3 Theskyisblue… Row 3 data More Row 3 data 4 GMCTruckare… Row 4 data More Row 4 data 5 DodgePowerWagon… Row 5 data More Row 5 data 6 Thegrassisgreeneron… Row 6 data More Row 6 data <colgroup><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Note that I have purposely removed the spacing as the Column-A needs to "contain" the letters and not match a word. As long as somewhere in the cell it has the combination of characters, it contains it and the complete ROW needs to be MOVED to another sheet. If nothing matches, then it is not processed and stays in the current sheet.

In the CRITERIA sheet you can see there are now two columns with the headers of CARS and COLOR. When macro finishes I should have two new sheets, one CARS and the other COLOR.

The other twist is that should I add a third column to CRITERIA it should be processed as well. So we have two open dimensions, the size of the elements under each header and the number of headers to be processed against the DATA.

I did not add a no-match row so that it would stay in the DATA sheet...

I have to run, so I will read through this later today to make sure it makes sense.

Thanks for your time,

Rich

 

[mumps]

If you don't want the blank line, then you can delete it. Add this line of code:

Sheets(category.Value).Rows(1).EntireRow.Delete

below this line of code

Next val

(2 occurrences)

 

[rjbur]

I'm working through the code and stepping slowly through it as I have set a number of variables to be "watched" an noted a strange behavior... ok so it seems strange to me...

The portion of code where the "For" loop starts looks like:

For Each category In Sheets("Criteria").Range(Cells(1, 1), Cells(1, lColumn1))    
        Set ws = Nothing     
        On Error Resume Next
        
        Set ws = Worksheets(category.Value)
        
        On Error GoTo 0
        
        
        If ws Is Nothing Then.........

So as I watch the variables and I do see that Category.Value does get set prior to the line:

        Set ws = Worksheets(category.Value)

However, the value of "ws" remains set to "Nothing" as it was in the line:

        Set ws = Nothing

So when it gets down to the "IF" statement, "ws" is still nothing??

  If ws Is Nothing Then

Why does "ws" not get assigned as I would have expected?

Thanks,

Rich

 

[mumps]

At the beginning of the loop, ws is set to nothing which refreshes the variable. The name of ws is then set to the value of 'category'. The first part of the 'If...Else' loop, tests to see if the worksheet with that category name exists. Even though ws is set to category.value, that sheet doesn't exist so ws is therefore still nothing and the sheet is created and the data is copied to that sheet. If the sheet already exists, the loop skips to the 'Else' part and just copies and pastes the data to the already existing sheet. I hope this makes sense.

 

[rjbur]

At the beginning of the loop, ws is set to nothing which refreshes the variable. The name of ws is then set to the value of 'category'. The first part of the 'If...Else' loop, tests to see if the worksheet with that category name exists. Even though ws is set to category.value, that sheet doesn't exist so ws is therefore still nothing and the sheet is created and the data is copied to that sheet. If the sheet already exists, the loop skips to the 'Else' part and just copies and pastes the data to the already existing sheet. I hope this makes sense.

Thanks, makes perfect sense. I'm going to continue to study and learn and thank you again for your time and patience as I wrap my mind around how Excel's macro language works... I'm going to add this explanation into the comments and continue to dive deeper later today into the code...

Rich

 

[mumps]

It was a pleasure and good luck with it.