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

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

You cant paste this here because the copy area and paste area aren't the same size
Vba copy data from one sheet to another if a condition is met - Best answers
Copy data from one sheet to another in excel based on criteria - Best answers
Transfer data from one excel worksheet to another automatically - Guide
How to reset safe folder password without losing data ✓ - Android Forum
Google sheet right to left - Guide
How to find specific words on a page - Guide
Windows network commands cheat sheet - Guide

3 responses

Answer 1 / 3

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 16, 2019 at 12:04 PM

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
May 17, 2019 at 03:34 AM

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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 20, 2019 at 11:28 AM

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
May 28, 2019 at 07:15 AM

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.

Answer 2 / 3

xpcman Posts 19530 Registration date Wednesday October 8, 2008 Status Contributor Last seen June 15, 2019 1,825
May 6, 2019 at 12:59 PM

I suggest you read this:

https://ccm.net/forum/affich-850855-copy-a-row-from-one-worksheet-to-another-based-on-criteria#answers

TomasSurvilas Posts 7 Registration date Monday May 6, 2019 Status Member Last seen June 4, 2019
May 7, 2019 at 02:28 AM

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
May 7, 2019 at 08:07 AM

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
May 7, 2019 at 08:25 AM

Exactly as I'm not a code professional, thus wonder whether can get any help towards my problem.

Blocked Profile
May 7, 2019 at 09:10 AM

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
May 7, 2019 at 09:18 AM

thanks

Answer 3 / 3

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 28, 2019 at 11:33 AM

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
Jun 4, 2019 at 03:41 AM

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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 4, 2019 at 12:06 PM

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

Similar discussions

Copying data from one Excel sheet to another.

Excel VBA copy and paste loop with logic condition

Copy rows based on a condition

Copy data onto sheet only if cell has text

Code to move rows into another sheet based on certain values

Copy rows to new sheets if condition is met

Similar discussions

Subscribe To Our Newsletter!