Automatically transferring rows of data between sheets

[Closed]
Report
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Hello,

I am creating an excel spreadsheet that will be used to keep track of my organization's board member and supporter attendance at events. I want to have one sheet that contains our entire email list for invites, and I am hoping to have the second sheet automatically transfer information for those who respond "yes" to invites - an automatic RSVP system to keep track of who is coming to events. Member information includes first name, last name, and email address, so it would require copying an entire row and not just a single cell. Is this possible?

If not, is there a simple code that would allow me to select all rows with matching "yes" criteria and just copy and paste those over to another sheet?

Thanks!

2 replies

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello JZendran,

The following code should do as you require:-


Sub Transfer()

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    With Range("I1", Range("I" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Yes"
        On Error Resume Next
        .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
    End With
    .AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet2.Select

End Sub


Following is the link to my test work book for you to peruse:-

https://www.dropbox.com/s/z97mdpha79erv6e/JZendran%28Transfer%20from%20sht1%20to%20sht2%2Cautofilter%29.xlsm?dl=0

The code filters Column I for the criteria "Yes" and then transfers the relevant row of data to Sheet2.
Type "Yes" in any cell in Column I and then click on the "RUN" button to see it work.

As I don't know the exact set out of your work book, you may have to change the cell reference for the criteria.

Anyway, let us know your thoughts.

I hope that this helps.

Cheerio,
vcoolio.
Thanks for your help. I tried editing the code for my worksheet but I must have done it wrong because it isn't copying the information. The "yes" responses will appear in column B, and I want to copy from Sheet1 "Invite List" to Sheet5 "Invite Confirmations". This is what I have for the code so far, could you please help me fix it for my sheet?

Sub Transfer()

Application.ScreenUpdating = False

With ActiveSheet
.AutoFilterMode = False
With Range("B2", Range("B" & Rows.Count).End(xlUp))
.AutoFilter 1, "Yes"
On Error Resume Next
.Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
End With
.AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet5.Select

End Sub

Thank you!!
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello JZendran,


In your case, two minor adjustments to the code should work;-


Sub Transfer()

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter 1, "Yes"
        On Error Resume Next
        .Offset(1).EntireRow.Copy Sheet5.Range("A" & Rows.Count).End(3)(2)
    End With
    .AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet5.Select

End Sub


You'll note that:-

- the code refers to the "Activesheet" which is, I assume, your input sheet (or Sheet1). No need to alter this.

- on line 7 I have changed B2 to B1 as the filter needs to be placed on row1 (the headings row) and not the first row of data (row 2).

- on line 10 I have changed Sheet2 to Sheet5.

In the VBA Editor, to the left of the main code field, you'll see the VBA Project Explorer pane. Here you will see a list of your work sheets with the actual sheet code (Sheet1, Sheet2 etc.) with the sheet name to the right of the sheet code. Check that the sheet codes match the sheet name and make sure that the correct sheet code is in the code above. You can, of course, reference the sheet name in the code if you prefer. For example, if Sheet5 is named "Invite Confirmations" then in the code you can refer to it as:-

 Sheets("Invite Confirmations")


The code is written as you see it above in order to keep it as trim as possible. There are other ways of doing this such as declaring a worksheet variable and then setting a value to that variable but I don't want to overload your brain too much at this point!

Let us know how it works out for you.

Cheerio,
vcoolio.