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

Please Login or Register  to view this content.

[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

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

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.