Automatically transferring rows of data between sheets
Closed
jzendran
-
Jul 15, 2016 at 04:51 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 18, 2016 at 07:58 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Jul 18, 2016 at 07:58 PM
Related:
- Automatically transferring rows of data between sheets
- Transfer data from one excel worksheet to another automatically - Guide
- Download automatically while roaming - Guide
- Tmobile data check - Guide
- Sheets right to left - Guide
- Why does facebook refresh itself automatically - Guide
2 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jul 16, 2016 at 12:50 AM
Jul 16, 2016 at 12:50 AM
Hello JZendran,
The following code should do as you require:-
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.
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.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Jul 18, 2016 at 07:58 PM
Jul 18, 2016 at 07:58 PM
Hello JZendran,
In your case, two minor adjustments to the code should work;-
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:-
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.
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.
Jul 18, 2016 at 04:43 PM
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!!