How to copy a data from one sheet to another, when specific criteria is met
Closed
TomasSurvilas
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
May 6, 2019 at 08:47 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 4, 2019 at 12:06 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 4, 2019 at 12:06 PM
Related:
- Vba copy data from one sheet to another if a condition is met
- Excel copy data from one sheet to another if a condition is met - Best answers
- Excel copy row to another sheet if condition is met - Best answers
- Transfer data from one excel worksheet to another automatically - Guide
- Vba case like - Guide
- Little alchemy cheat sheet - Guide
- Vba code to copy data from one sheet to another based on criteria ✓ - Excel Forum
- Google sheet right to left - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
May 16, 2019 at 12:04 PM
May 16, 2019 at 12:04 PM
Hi Tomas,
Here you go:
To implement the code:
- Hit Alt+F11 to open the VBA window
- Find Sheet1 on the left side and double-click it
- Paste the code in the big white field
- The VBA window can now be closed
Best regards,
Trowa
Here you go:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Columns("C"), Target) Is Nothing Then Exit Sub
Rows(Target.Row).Copy Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
End Sub
To implement the code:
- Hit Alt+F11 to open the VBA window
- Find Sheet1 on the left side and double-click it
- Paste the code in the big white field
- The VBA window can now be closed
Best regards,
Trowa
May 17, 2019 at 03:34 AM
Many thanks for your support, it works well, but as I'm not that professional as you are, have additional question. When I'm trying to change the column to lets say "G", instead of "C", it returns with an error: "you can't paste this here because the Copy area and the paste area aren't the same size". Any clue on this please?
May 20, 2019 at 11:28 AM
That is because you can only paste an entire row in the first column. Otherwise Excel runs out of columns to paste the data in.
Since I didn't know which columns contain data, I used column C, which contains data for sure, to determine the last row used on Sheet2. Then used Offset to paste 2 columns to the left to end up in column A.
When column C always contain data you can leave the 3rd code line alone and just change the C into G on the 2nd code line.
When you change the C into a G on the 3rd code line, you also have to change the Offset value from (1,-2) into (1,-6), meaning 1 cell down and 6 to the left.
Hopefully that clears things up.
Best regards,
Trowa
May 28, 2019 at 07:15 AM
Many thanks for the support, now it is clear, tested and works, brilliant.
Can I ask you for another advice - what is the way the copied row would appear with a date indication (date would appear in the same copied row, separate column). Like we have a row "1/2/3/4" and copied row would now have a date in it "1/"date"/2/3/4".
many thanks for support.