I need help editing my code so that it does something more specific. Currently the code separates all data from a "Data" worksheet in to separate corresponding worksheets using the "Name of Opportunity" column. I need it so that it separates depending on what the user wants it to separate by. So for example, in field W11 on a separate worksheet called "Diagram" the user can enter "Co" as a opportunity and when they click the "Split Data" button on the same worksheet it should only split by "Co" and put it in a separate worksheet called "Opportunity" 

**Here is the scenario I am trying to achieve:**

1. User enters an opportunity name in the “Diagram” worksheet in field W11
2. User presses “Split Data” button in “Diagram” worksheet
3. A separate worksheet is automatically created called “Opportunity”
4. Looks-up the “Name of Opportunity” column in the “Data” worksheet and compares it with the user entry (step 1)
5. All the data that corresponds with the users entered field (step 1) will be copied over into the newly made “Opportunity” worksheet – This includes the entire row (all 4columns A-D of that specific entry).

Example: If a user types in "Co" in the W11 field and then presses the "Split Data" - all the "Co" opportunities will be put in a separate worksheet (called "Opportunity")

[Data Worksheet](https://i.stack.imgur.com/BbCnp.png)

[Diagram Worksheet](https://i.stack.imgur.com/HFYdG.png)

**Assumptions:**

* The user can press the “Split Data” button again and it should re-do the process (Overwrite the “Opportunity” worksheet)
* As the data on the "Data" worksheet will be always increasing the range that it looks up should be end of row

**What I have done**

As stated above I am struggling to get the code to be more specific (unsure how to go about editing the code - can't find anything online that helps me understand). I am currently able to split all data into different worksheets but I need it only to be split by what the user wants. Here is the code I have below:

```
Private Sub CommandButton2_Click()

Const col = "A"
Const header_row = 1
Const starting_row = 2
Dim source_sheet As Worksheet
Dim destination_sheet As Worksheet
Dim source_row As Long
Dim last_row As Long
Dim destination_row As Long
Dim Opp As String

Set source_sheet = Workbooks("CobhamMappingTool").Worksheets("Data")
last_row = source_sheet.Cells(source_sheet.Rows.Count, col).End(xlUp).Row

For source_row = starting_row To last_row
    Opp = source_sheet.Cells(source_row, col).Value
    Set destination_sheet = Nothing
    On Error Resume Next
    Set destination_sheet = Worksheets(Opp)
    On Error GoTo 0
    If destination_sheet Is Nothing Then 
        Set destination_sheet=Worksheets.Add(after:=Worksheets(Worksheets.Count))
        destination_sheet.Name = Opp
        source_sheet.Rows(header_row).Copy Destination:=destination_sheet.Rows(header_row)
    End If
    destination_row = destination_sheet.Cells(destination_sheet.Rows.Count, col).End(xlUp).Row + 1
    source_sheet.Rows(source_row).Copy Destination:=destination_sheet.Rows(destination_row)
Next source_row

End Sub

```

Any help is appreciated

Many thanks,
James