create a worksheet based on description

[endout]

Hi,

I have a worksheet filled with data of manufacturers and products. I've managed to automatically create worksheets for different manufacturers and their products. However, I'd like to go one step further and include the product description in those worksheets.
So far I'm looping through the manufacturers and products aönd create a worksheet for individual products (listed in a help worksheet), which looks something like this:
manufacturer1 | product1
manufacturer2 | product2

I'd like to add another column which should be included in my macro, so that it may look like this:
manufacturer1 | product1 | desc1;desc2
manufacturer2 | product2 | desc 3;desc4

As you may see the descritption will be seperated by a semicolon (or any other sign, if that might cause problem) and my data will look like this:
manufacturers | products | string with description

The description contains more data, than the descx in the second table, however that descx wil have an identical spelling to the string im looking for in the description.

Now, is it possible to compare the string i have with the data-string (which as i said contains more then just the values I'm looking for) and is it possible to have the strings I'm looking for all in one cell (with a defined seperating sign) or do they all have to be in individual cells?
If that is possible, how would the code for the comparison look like?
I would use the for-loop I've used for the manufacturer and product, but I have no idea how t include the description in that.

Thank you.

[SamT]

We will need to see the first worksheet, (5 products, 2 manufacturers only please,) and how you decide which parts of the description you want.

We also need to see one of the resulting sheets, using only the data from the first sample (5 products)

Include your existing code.

If you use the Go Advanced button, You can use the Paperclip Icon on the Advanced Editor to attach the workbook(s)

[endout]

Hi,

Here's what I wrote in my initial Post as a workbook. sheet2 contains the data, TMP stores the cluster where the manufacturer, product and description is stored (easier in the long term for the enduser) and the endresult should look like the template.
My code for the sheer copying is included in the workbook but looks like this:

Sub Copy()
Dim Maker_Name as String, Model_Name As String
Application.Calculation = xlManual
Y = Sheets("TMP").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To Y
Maker_Name = Sheets("TMP").Range("A" & i).Value
Model_Name = Sheets("TMP").Range("B" & i).Value
Sheets("template").Copy After:=ActiveSheet
Sheets("template (2)").Name = Model_Name
Next i
Application.Calculation = xlAutomatic
End Sub

However this only creates sheet for the products. As you can see the description will be stored as shwon in the shet "TMP" and will always be at the start of the corresponding cell in sheet2. Furthermore the spelling in the TMP-Sheet will be identical to the speling in sheet2.
Basically I want to create sheets for grouped products i.e product1 made by manufacturer1 with desc1 and desc2 should create one sheet. An real world example would be to group all screws which are smaller than 4mm in one sheet, while bigger ones are ignored.
The manual calculations during the copy are there because some computers of the enduser might be slow and the calculations will be done after all sheets are created rather than inbetween.

Update:
I've managed to create some code which lets me split the description (so far it only cuts out the first part, but cuttiong the rest will work just as easiyl):

Sub DescSplitter()
Dim position As Integer
Dim substring As String
Dim lastrow as integer

y = Sheets("TMP").Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To y
position = InStr(Cells(i, 3), ";")
If (position > 0) Then
substring = Replace(Left(Cells(i, 3), position - 1), "[", "")
End If
Next i
End Sub

Now to store these values and use them to create a new sheet I would probably need to use an array, wouldnt I? If so how would I go on in creating one? There are multiple posible cases for the description-column used fo filtering. It could either be empty or filled with up to 3 values (at least for now).

[endout]

Sorry for another post, the old one can't be edited anymore. Maybe a mod will join them together.
I've also realised that the sheer copying is not really the problem but rather the formula/counting in the copied sheet. Here's the code to simply count the products of one manufacturer (so far based only on the manufacturer and product and ignoring the description).


Public Function Amount(Head1 As String, Head2 As String, Period As Date)
Dim HeadLine As String
Dim Amt As Double
Dim x, y, Last_Column, Last_Row As Integer
Dim Col_Head1, Col_Head2, Col_Period as Integer
Dim Year_Set, Month_Set As Integer
Dim Year_Filter, Month_Filter As Integer
Last_Column = data.Cells(1, Columns.Count).End(xlToRight).Column
For y = 1 To Last_Column
HeadLine = data.Cells(1, y)
If HeadLine = "Head1" Then
Col_Head1 = y
End If
If HeadLine = "Head2" Then
Col_Head2 = y
End If
If HeadLine = "Date" Then
Col_Period = y
End If

Next y
Amt = 0
Last_Row = data.Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To Last_Row
If data.Cells(x, Col_Head1) = Head1 And data.Cells(x, Col_Head2) = Head2 Then
Year_Set = Val(Mid(data.Cells(x, Col_Period), 7, 4))
Month_Set = Val(Mid(data.Cells(x, Col_Period), 4, 2))
Year_Filter = Year(Period)
Month_Filter = Month(Period)
If Year_Set = Year_Filter And Month_Set = Month_Filter Then
Amt = Amt + 1
End If
End If
Next x
Amount = Amt
End Function

[SamT]

We will need to see the first worksheet, (10 products, 3 manufacturers only please,) and how you decide which parts of the description you want. Real product words and rel descriptions, please

We also need to see some of the resulting sheets, using only the data from the first sample (5 products)

Don't include any temporary sheets. We probable won't do it the way you imagine.

Your posts, code, and samples don't completely match each other

[endout]

I can't provide live data, since I myself am only working with test data (which resembles what I've uploaded).
Furthermore, there are now two resulting sheets including the counter. However, as you may see it will count every instance of a specific product and will not include the description.
The temporary sheet is not really temporary but will remain in it's form since the enduser will need some way of putting in the specidifc manufcaturers, products and descriptions they want included in the report.
Also the code is now fully functional (except for the descriptions).

As you may see in the TMP-Sheet the needed descriptions will be filled in one cell (or multiple cells if that should be better or easier) and will be seperated by a semicolon.
The DescSplitter Sub will then seperate the descriptions and store them as variables (but I don't know how that would be best, maybe an Array?). Afterwards a sheet is created which resembles the template but the Amount-Function will only increment if the product is actually the one in the temp sheet. As you may see in the data sheet the column with the description always has what I'm looking for at the beginning of the cell.
In my example the amount counted in the sheet prod1 should not be 8 but 3, since only prod1 with the desc1 should actually be included.

[SamT]

So Tmp is identical to Data, without duplicates and with a shorter description. The Product sheets are just Transposed, (vertical,) Tables without Descriptions.

[endout]

For the sake of code creating, yes. The live data will have ~10k records with 10-15 manufactureres and ~100 products. Also the layout will be improved later and more will be added, but that's not important to my problem, since I'ts basically identical to this sheet.
The layout of the product sheets will be more sophisticated and will store more different data, but creating those won't be a problem, as soon as i figure out how to include the descriptions in the formulas.
TMP will probably only hold about 20 records, which will be significantly less than the data sheet, but it has to be there, because I think a solution like that will be the easiest way for a standard excel user to input their filter criteria.

[SamT]

the code is now fully functional (except for the descriptions)....
as soon as i figure out how to include the descriptions in the formulas.

I'm not really good with Formulas, but in code I would use something like

Dim tmp '(as variant)
tmp = Spit(DescriptionCell, ";")
Description = tmp(0) & " - " & tmp(1)