Create a button that generate specify column to a new worksheet

Question

I have just post a question last week. The question is about finding a the missing cell in a list. It is the post link Button click find empty cell Base on the previous question i would like to add on some new function. Red column is mandatory field and green is optional field. I would like to add a new column and create a button that could generate all the mandatory fields to a new worksheet.

As you can see, there are a new column and a new button that was created. Now, I would like to use the "generate button" to generate all the mandatory(Red) fields into a new worksheet call "important". However, it is I have a condition rely on the column K to decide which row of data i will generate. If col K is YES than it will generate that row of data if col K is No then I will not generate that row to the new sheet.

My problem is how can i generate a new worksheet and fetch those mandatory fields to a new worksheet with yes or no condition in col K Thank you

It is my code :

Private Sub CommandButton2_Click()

Dim rng As Range
Dim selected As Range
Dim newws As Worksheet
Dim yesno As Range
Dim lastrow As Long
Dim justify As Boolean

lastrow = Range("B3").End(xlDown).Row

Set rng = Range("B3:J" & lastrow)
Set yesno = Range("K3:K" & lastrow)

Worksheets("Important").Add after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
 For Each selected In rng
If rng.Cells.Value = "Yes" Then
justify = True
Worksheets("Important").Copy
Else
If rng.Cells.Value = "No" Then
justify = False

Set newws = Nothing
End If
End If
Next
End Sub

Answer 1

A couple of things:

1. You are cycling through column B and not Column K. The For Each loop should be For each selected in yesno not rng.

2. The method by which you are creating the sheet will not work. I like to break them into two lines. I declares a worksheet variable 'tws'.

   Set tws = Worksheets.Add(after:=Sheets(Worksheets.Count)) tws.Name = ("Important")

3. The copy/paste line is incorrect. The syntax is range(source).copy range(destination)

So all together the following will do what is asked:

Private Sub CommandButton2_Click()

Dim rng As Range
Dim ss As Range, cel As Range
Dim yesno As Range
Dim lastrow As Long
Dim justify As Boolean
Dim tws As Worksheet
Dim tlr&, i&

Set wks = Sheets("Sheet1") 'Change this to the worksheet with the data
With wks
    lastrow = .Range("B3").End(xlDown).Row

    Set yesno = .Range("K3:K" & lastrow)

    Set tws = Worksheets.Add(after:=Sheets(Worksheets.Count))
    tws.Name = ("Important")
    Set rng = Union(.Range("B3"), .Range("D3"), .Range("E3"), .Range("H3"), .Range("I3"))
    rng.Copy tws.Range("B1")
    For Each ss In yesno
        If LCase(ss.Value) = "yes" Then
            'set rng to the 5 cells desired.
            Set rng = Union(.Range("B" & ss.Row), .Range("D" & ss.Row), .Range("E" & ss.Row), .Range("H" & ss.Row), .Range("I" & ss.Row))
            tlr = tws.Range("B" & tws.Rows.Count).End(xlUp).Offset(1).Row 'find next empty row on target sheet
            rng.Copy tws.Cells(tlr, "B") 'Change to starting column desired.
            justify = True 'not sure what this is for.
        ElseIf LCase(ss.Value) = "no" Then
            justify = False 'not sure what this is for.
        End If
    Next
End With
End Sub

I changed the selected to ss just to stay away from selection and so I did not need to type so much.

I also declared rng as the required cells to copy.

Edited to add titles