Link Worksheet Change Event to Button

[kristinh]

I am new to VBA code. I wrote a worksheet change event that copies rows from one tab to another based on whether there is a Y is a certain column. It works great, but it is slow. I would like to modify the code so the rows are copied over upon click of a button instead of each change... but I don't know where to begin or how to assign a button to this?

This is my worksheet:

This is my code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub
If Target = "Y" Then
Range("A" & Target.Row).Resize(, 5).Copy Sheets("GP Import").Cells(Sheets("GP Import").Rows.Count, "A").End(xlUp).Offset(1)
End If
Application.ScreenUpdating = False
End Sub

(Note that the button Generate Accounts is not actually doing anything at this point)

 

[rollis13]

Below is a revised version that replaces the event macro.

Option Explicit
Sub Update_If_Y()
    Dim i     As Long
    Dim lr    As Long
    lr = Cells(Rows.Count, 6).End(xlUp).Row
    Application.ScreenUpdating = True
    For i = 12 To lr
        If Cells(i, 6) = "Y" Then
            Range("A" & i).Resize(, 5).Copy Sheets("GP Import").Cells(Sheets("GP Import").Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Keep in mind that when it starts, unlike before, the macro will process all rows where it finds a Y, even if it has been run before. Instead, the event macro processed each row only once, and only when a Y was entered.

 

[kristinh]

Wow, that worked perfect. I never in a million years would have been able to do that myself. Can I ask you go about learning this? I want to be able to automate my accounting workbooks with VBA, but my degree is in accounting. Is it even realistic for someone to learn VBA as a hobby/side job?

Thank you so much for your time and help!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help. By the way, you probably need to mark this thread as [Solved].
Yes, as far as learning VBA is concerned this can be done as a "hobby", all you have to do is insist by searching the web for snippets to insert and adapt to your macros. Then, always on the web, you will find many guides that explain how to put together all the information found. And, when you don't understand/succeed, ask in the Forums where you will always find help.