1

I have 1 sheet with 10,000 + rows I want to copy data based on criteria. I found one VBA that works, but it would require me to edit the VBA for each criteria and change the destination of the copied cells. I will do this, but I know there has to be a more efficient way to get this done.

Below is the code I'm currently using:

Private Sub CommandButton1_Click()

a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 6 To a

    If Worksheets("Sheet1").Cells(i, 2).Value = **"Group 1"** Then

        Worksheets("Sheet1").Rows(i).Copy
        Worksheets("Sheet2").Activate
        b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Sheet2").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("Sheet1").Activate

    End If

Next

Application.CutCopyMode = False

ThisWorkbook.Worksheets("Shee1").Cells(5, 1).Select

End Sub

Column 2 has 36 groups which means I would have to edit the VBA 36 times to get the results I need. Any suggestions would be appreciated.

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
FavoriteAnalyst
  • 11
  • 1
  • 5
    Use a filter instead of looping row by row. – BigBen Nov 18 '19 at 15:32
  • 3
    You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ Nov 18 '19 at 15:33

0 Answers0