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

Report
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019
-
Posts
2652
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 10, 2020
-
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

3 replies

Posts
19674
Registration date
Wednesday October 8, 2008
Status
Contributor
Last seen
June 15, 2019
6,363
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.
Blocked Profile
So thomas, you cannot modify that code to fit your own model?
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.
Blocked Profile
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!
Posts
7
Registration date
Monday May 6, 2019
Status
Member
Last seen
June 4, 2019

thanks
Posts
2652
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 10, 2020
439
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
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?
Posts
2652
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 10, 2020
439
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
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.
Posts
2652
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 10, 2020
439
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

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.
Posts
2652
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 10, 2020
439
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