How to copy a data from one sheet to another, when specific criteria is met

Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
- - Latest reply: TrowaD
Posts
2566
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 19, 2019
- Jun 4, 2019 at 12:06 PM
Hello,
If, for instance, the criteria changes in column "C" - it gets higher number, how to transfer the row data to other sheet2, copying the row data.
For example - originally it was 0 in column "C" of row 22, then it has changed to 1 in the same row 22 of column "C". Then I need all the data in row 22 to be copied to other sheet - sheet2.
Data, in sheet2, should be copied in a row so when another row in sheet1 of column "C" would have change, data has to be copied into sheet2 in addition to data already have been transferred (down to another row after the data already copied).



System Configuration: Windows / Chrome 73.0.3683.103
See more 

3 replies

Posts
19676
Registration date
Wednesday October 8, 2008
Status
Moderator
Last seen
June 15, 2019
5605
TomasSurvilas
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
Hi there,

It does not do the trick.
As a matter of fact, master sheet contains data, which does not change except the quantity of stock. Once the stock quantity changes (the number increases by 1 in column "C"), then I need the row data, which is affected, to be copied into new sheet2. Then the cycle repeats. In master sheet the stock might change in different row and then again whole row has to be copied to the sheet2, next (or bellow the row) to the data already there.
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1537 -
So thomas, you cannot modify that code to fit your own model?
TomasSurvilas
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
Exactly as I'm not a code professional, thus wonder whether can get any help towards my problem.
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1537 -
Hang tight, someone will cone along and do your work for you. I would assist if you had questions about the code. But since ypu are asking for a turn key solution, wait around, there are experts that are willing to provide a cut and paste solution for ya!
TomasSurvilas
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
thanks
Respond to xpcman
Posts
2566
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 19, 2019
370
0
Thank you
Hi Tomas,

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
TomasSurvilas
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
Hi Trowa,
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?
TrowaD
Posts
2566
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 19, 2019
370 -
Hi Tomas,

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
TomasSurvilas
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
Hi Trowa,
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.
Respond to TrowaD
Posts
2566
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 19, 2019
370
0
Thank you
Hi Tomas,


So whenever a change is made in Sheet1 column C, copy the row to Sheet2 first available row, where the current days date will be inserted in column B.

For that use:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Integer

If Intersect(Columns("C"), Target) Is Nothing Then Exit Sub

lRow = Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Row + 1

With Sheets("Sheet2")
    Rows(Target.Row).Copy .Range("C" & lRow).Offset(0, -2)
    .Range("B" & lRow).Insert shift:=xlToRight
    .Range("B" & lRow).Value = Date
End With

End Sub


Best regards,
Trowa

TomasSurvilas
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
Hi Trowa,
Many thanks for your help.
May I ask another favor: I need the copied row to end up to certain column. Let's say presently it copies all the row, but I need information to by copied only up to column "O".

Thanks for help.
TrowaD
Posts
2566
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 19, 2019
370 -
Hi Tomas,

Alright, for that look at the 9th code line at this part:
Rows(Target.Row).Copy

and change it into:
Range(cells(target.row,"A"),cells(target.row,"O")).copy

Best regards,
Trowa
Respond to TrowaD