Merging multiple command buttons into one

[antzint]

Hello,
I have a question here.
--> I have five Command buttons in my excel sheet which is basically performing the filter and copy task. Since all of them are kind of doing the same task, I would like to merge the five Command buttons into one. Is that possible?

I would be very thankful if anyone of you could help me out in this.

Please find my code below for reference:

---

Private Sub CommandButton1_Click()
x = 3
Do While Cells(x, 3) <> ""
If Cells(x, 3) = "Y1" Then
Worksheets("Design Cause Notifications").Rows(x).Copy
Worksheets("For Y1").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("For Y1").Rows(erow)
End If
Worksheets("Design Cause Notifications").Activate
x = x + 1
Loop
End Sub

---

Private Sub CommandButton2_Click()
x = 3
Do While Cells(x, 3) <> ""
If Cells(x, 3) = "Z1" Then
Worksheets("Design Cause Notifications").Rows(x).Copy
Worksheets("For Z1").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("For Z1").Rows(erow)
End If
Worksheets("Design Cause Notifications").Activate
x = x + 1
Loop
End Sub

---

Private Sub CommandButton3_Click()
x = 3
Do While Cells(x, 3) <> ""
If Cells(x, 3) = "Z2" Then
Worksheets("Design Cause Notifications").Rows(x).Copy
Worksheets("For Z2").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("For Z2").Rows(erow)
End If
Worksheets("Design Cause Notifications").Activate
x = x + 1
Loop
End Sub

---

Private Sub CommandButton4_Click()
x = 3
Do While Cells(x, 3) <> ""
If Cells(x, 3) = "Z4" Then
Worksheets("Design Cause Notifications").Rows(x).Copy
Worksheets("For Z4").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("For Z4").Rows(erow)
End If
Worksheets("Design Cause Notifications").Activate
x = x + 1
Loop
End Sub

---

Private Sub CommandButton5_Click()
x = 3
Do While Cells(x, 3) <> ""
If Cells(x, 3) = "Y3" Then
Worksheets("Design Cause Notifications").Rows(x).Copy
Worksheets("For Y3").Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("For Y3").Rows(erow)
End If
Worksheets("Design Cause Notifications").Activate
x = x + 1
Loop
End Sub

---

Kind Regards,
Antz

[AB33]

Antz,
Please use code tags with your code.
I personally tried to avoid using a do/ while loop, and prefer for I loop

Sub merge()

        Application.ScreenUpdating = 0
        LR = Cells.Find("*", , , , xlByRows, xlPrevious).Row
        
        For i = 3 To LR
         
              Select Case Cells(i, 3)
            
                Case Is = "Y1"
                    
                    Worksheets("Design Cause Notifications").Rows(i).Copy
                    Worksheets("For Y1").Worksheets("For Y1").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
                Case Is = "Z1"
                
                    Worksheets("Design Cause Notifications").Rows(i).Copy
                    Worksheets("For Z1").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
                
                Case Is = "Z2"
                
                     Worksheets("Design Cause Notifications").Rows(i).Copy
                     Worksheets("For Z2").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
                    
                Case Is = "Z4"
                
                     Worksheets("Design Cause Notifications").Rows(i).Copy
                     Worksheets("For Z4").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
                     
                Case Is = "Y3"
                
                     Worksheets("Design Cause Notifications").Rows(i).Copy
                     Worksheets("For Y3").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
            
             End Select
         
        Next i
        Application.CutCopyMode = 0

        Application.ScreenUpdating = True
     
End Sub

[antzint]

Hi,
Thank you for your assistance. But, can I ask you how should I run this when I click my commandbutton1 in my worksheet.
Kind Regards,
Antz

[AB33]

If you have already running your codes from button then
change this line of the code

Sub merge()

INTO

 Private Sub CommandButton1_Click()

Means the code is linked to button 1

[antzint]

Hi again,
I am really sorry for this stupid question when I had tried to write a code that big like that above. I had actually fixed it. But Thank you anyways (Your code works like a charm)
Could you give me an example how should I add a code tag ?. I will follow that hereafter
Also I agree with the I loop.
Many Thanks and Kind Regards,
Antz

[AB33]

It is really easy!
_highlight(Select the entire code) then click this sign # from the quick reply menu( you see this sign #-Above the post quickly, or go advance buttons). Done!
Or [code] your code here [code]
If you have already posted the code with out tag, you could go back to the code itself, then edit and do as above.

[antzint]

Hello,
Thank you very much
Kind Regards,
Antz

[AB33]

Antz

You are welcome!

Could you please now close (Mark) this thread as solved? Go in to the top right-hand side of this page, choose "Thread Tools" from the menu, then select "solved" from the drop down menu.