Export excel data into new workbook

Question

With some of the success of other automations, I am looking to see if it is possible to move rows of data from my excel workbook into a new one based upon the value in Column A matching the value in ComboBox1.

I have Sheet1 which has my data, a UserForm that contains ComboBox1.

What I think I am looking for is when ComboBox1 value is equal to the value in Column A of Sheet1, I want all rows that contain that value exporting to a new worksheet. I want this to be activated through CommandButton1.

All help is apricated, as I expect this to be setting up a loop - way outside my understanding of VBA!!

This is my attempt to date... I am getting a Runtime Error, I am assuming I am getting active and source workbooks all confused?

Dim row As Long

Private Sub CommandButton1_Click()
Workbooks.Add
NewBook = ActiveWorkbook.Name
ThisWorkbook.Activate
Workbooks(NewBook).Activate

    Dim s As Worksheet
    Set s = Worksheets("Engagement Programme Q1")
    Dim row As Long
    For row = 1 To s.Cells(s.Rows.Count, 1).End(xlUp).row
      If s.Cells(row, "A").Value = ComboBox1.Value Then
       ActiveWorkbook.Sheets("Sheet1").Range("A" & NextHeaderRow + 5).PasteSpecial xlPasteValues
    End If
        
Next

End Sub

Answer 1

Your title saying "new workbook". But in your question you say "new worksheet". I also still don't know if you want to "move" or "copy".

I'm sorry if I don't understand you correctly.

Anyway, maybe you want to have a look at this code.
Copy your original workbook first, then paste the code below to the copied one.

Sub test()
Dim rpl As String
Dim rg As Range

'rpl = ComboBox1.Value
rpl = "a" 'whatever value to test

With Sheets("Sheet1")
Set rg = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
End With

        With rg
        .Replace rpl, True, xlWhole, , False, , False, False
        .SpecialCells(xlConstants, xlLogical).Copy
            With Sheets.Add
                .Range("A1").PasteSpecial (xlValues)
                .Range("A:A").Replace True, rpl, xlWhole, , False, , False, False
            End With
        .Replace True, rpl, xlWhole, , False, , False, False 'this line brings back the value
        '.SpecialCells(xlConstants, xlLogical).Delete Shift:=xlUp 'this line shift delete the cell (contains the value) up.
        End With
        
End Sub

Assumed that on Sheet1 column A, each consecutive row contains something like this :
a
a
d
t
f
y
a
r
a
z

The variable rpl is your ComboBox1 value, but since this is only a test sub, let's assume that the ComboBox1 value is "a". So the variable rpl value is hard-coded with "a" in the test sub.

What the code do

1. set the range of the data in column A of Sheet1 (variable rg).
2. within this range (the rg variable), to all the rows which has "a" value, the code replace "a" with boolean data ---> TRUE
3. then the code copy the special cells of the rg where the cell type is constant and logical (so in this case, it will copy all row in column A which value is "TRUE")
4. then the code create a new sheet
5. paste the copied cell of Sheet1 to this new sheet cell A1
6. replace the value of the rows of column A of the new sheet from TRUE to "a"
7. brings back the "a" value in the rg ... OR if you want to delete "a"
8. use the next line which will delete the cell with "a" value by cell shift up.

on the newly created sheet, the data in column-A will be like this:
a
a
a
a

If you use point-7, you will see the data in column A Sheet1 just like the example above.

If you use point-8, the data in column A Sheet1 will be like this :
d
t
f
y
r
z

If the result is what you expect, just remove the line rpl = "a" then use the line rpl = ComboBox1.Value for your Private Sub CommandButton1_Click().

Please note, the code assumed that the items in the ComboBox1 also exist in column A Sheet1. So it will throw an error if the rpl value (the ComboBox1.value) does not exist in any row of column A Sheet1.

I wonder, why don't you just have the macro to run when the user selected the combo box ? is there anything else the user should do (before click the CommandButton1) besides selecting the value in the ComboBox1 ?