Macro to copy-paste range in row to different sheets based on specific cell value

excel vba copy data from one sheet to another based on cell value
macro to copy data from one sheet to another based on criteria
excel vba copy paste range another worksheet
excel vba copy cell value and paste to another cell
excel macro to copy and paste from one worksheet to another
excel copy row to another sheet based on cell value formula
excel vba copy cells based on criteria
excel vba copy rows to another worksheet based on multiple criteria

I have a workbook with 3 sheets: first one is the raw data sheet, then 2 target sheets. I would need a macro that would look at cell C in raw data sheet and based on the 2 values (YES or NO), will copy and paste the range A:Y in sheets 2, respectively 3. Example: if on C2 in raw data sheet i have YES, copy A2:Y2 and paste into sheet 2, same range A2:Y2. If instead i have the value NO, copy A2:Y2 and paste into sheet 3. Then go to next row and copy-paste A3:Y3 to sheet 2 if YES or A3:Y3 to sheet 3 if NO.

I wrote something that only works for the 2nd row, but i don't know how to make it loop... so basically when it passes to the next rows, it still copies the values from A2:Y2 to the target sheet, instead of copying A3:Y3, A4:Y4 etc.. Pasting my poor code below:

Sub IdentifyInfraction()

    Dim rngA As Range
    Dim cell As Range

    Set rngA = Range("C2", Range("C65536").End(xlUp))
    For Each cell In rngA

        Worksheets("raw_data").Select
        If cell.Value = "YES" Then
            Range("A2:Y2").Copy
            Worksheets("Value_YES").Select
            Range("A2").PasteSpecial Paste:=xlPasteValues

        ElseIf cell.Value = "NO" Then
            Range("A2:Y2").Copy
            Worksheets("Value_NO").Select
            Range("A2").PasteSpecial Paste:=xlPasteValues

        End If

    Next cell

End Sub

Please help!!! :-s

Easiest solution would just be to replace the number 2 in each of your ranges to a variable which you then increment at the end your statement, before you go to the next cell.

For example:

Dim i = 2

Set rngA = Range("C2", Range("C65536").End(xlUp))
For Each cell In rngA

Worksheets("raw_data").Select
If cell.Value = "YES" Then
    Range("A" & i & ":Y" & i).Copy
    Worksheets("Value_YES").Select
    Range("A" & i).PasteSpecial Paste:=xlPasteValues

ElseIf cell.Value = "NO" Then
    Range("A" & i & ":Y" & i).Copy
    Worksheets("Value_NO").Select
    Range("A" & i).PasteSpecial Paste:=xlPasteValues

End If
i = i + 1
Next cell

So, originally we set i = 2, this is to go in line with your starting row of 2 mentioned in your question. Then, Range("A" & i & ":Y" & i).Copy is the same as saying Range("A2:Y2").Copy or Range("A3:Y3").Copy, etc.

This will go through any copy each row, a new row each time, and paste it to the respective row in the various sheets.

I hope this works for what you are trying to do, if not let me know.


There are a few things I'd also recommend looking into. There's a much better way to copy and paste, without going back and forward through the sheets.

ThisWorkbook.Sheets("raw_data").Rows(i).Copy Destination:=Worksheets("Value_YES").Range("A" & i)

Something like this would take the whole row from raw_data and transfer it to Value_YES. You'd have to mess around with it and change the range from Rows(i), but that's just an example.

I'd also recommend that you look into How to avoid using Select in Excel VBA to better understand why it's frowned upon to use Select and Activate in Excel VBA.

How to move entire row to another sheet based on cell value in Excel?, How do I copy a row based on a cell value to another sheet? Hello Excellers and welcome to another Excel Tip in my #macromonday 2019 series. Today let’s look at how to copy cells based on specific criteria in your Excel worksheet. This is a macro I created to help speed up a repetitive process at work. I needed to copy and paste cells that were greater than zero.

My version:

Sub GetR_Done()
    Dim rng As Range, c As Range, LstRw As Long
    Dim ws As Worksheet, Nr As Long, Yr As Long
    Dim Ys As Worksheet, Ns As Worksheet

    Set ws = Sheets("raw_data")
    Set Ys = Sheets("Value_YES")
    Set Ns = Sheets("Value_NO")

    With ws
        LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row
        Set rng = .Range("C2:C" & LstRw)
        For Each c In rng.Cells
            If c = "YES" Then
                With Ys
                    Yr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                End With
                .Range(.Cells(c.Row, "A"), .Cells(c.Row, "Y")).Copy Ys.Range("A" & Yr)

            End If

            If c = "NO" Then
                With Ns
                    Nr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                End With
                .Range(.Cells(c.Row, "A"), .Cells(c.Row, "Y")).Copy Ns.Range("A" & Nr)

            End If

        Next c
    End With
End Sub

If you really require to paste values, then use this one

Sub GetR_Done()
    Dim rng As Range, c As Range, LstRw As Long
    Dim ws As Worksheet, Nr As Long, Yr As Long
    Dim Ys As Worksheet, Ns As Worksheet

    Set ws = Sheets("raw_data")
    Set Ys = Sheets("Value_YES")
    Set Ns = Sheets("Value_NO")


    Application.ScreenUpdating = False
    With ws
        LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row
        Set rng = .Range("C2:C" & LstRw)
        For Each c In rng.Cells
            If c = "YES" Then
                With Ys
                    Yr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                End With
                .Range(.Cells(c.Row, "A"), .Cells(c.Row, "Y")).Copy
                Ys.Range("A" & Yr).PasteSpecial xlPasteValues

            End If

            If c = "NO" Then
                With Ns
                    Nr = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                End With
                .Range(.Cells(c.Row, "A"), .Cells(c.Row, "Y")).Copy
                Ns.Range("A" & Nr).PasteSpecial xlPasteValues

            End If

        Next c
    End With
    Application.CutCopyMode = False
End Sub

3 Ways to Copy and Paste Cells with VBA Macros in Excel, How do you write a macro to copy data from another worksheet? Sheets("Sheet1").Range("A1:D5").Copy Sheets("Sheet1").Range("A7:D11").PasteSpecial xlPasteFormulas Example 4: EntireRow copy and paste Sub EntireRowCopy() 'Paste the data of row 3 in row 8 Worksheets("Sheet1").Activate Rows(3).EntireRow.Copy 'PasteSpecial Method without argument copies the format as well.

you could try this:

Sub IdentifyInfraction()
    Dim cell As Range

    With Worksheets("raw_data") 'reference "raw data" sheet
        For Each cell In .Range("C2", .cells(.Rows.Count, "C").End(xlUp)) ' loop through referenced sheet column C cells from row 2 down to last not empty one
            Worksheets("Value_" & cell.Value).Range(cell.Address).Resize(, 25).Value = cell.Resize(, 25).Value 'have proper target sheet A:Y current cell row values as "raw data" sheet ones
        Next
    End With
End Sub

How to Use VBA Macros to Copy Data to Another Workbook in Excel, Learn 3 different ways to copy and paste cells or ranges in Excel with VBA Macros. Video Duration: 4:56 Posted: Jul 1, 2015 Using The Copy/PasteSpecial Method. This is the VBA code version of how you would manually paste values only in Excel with your mouse and keyboard. The code is simply copying a range and pasting the values only in a destination range.

Copying Rows between Worksheets Based on a Text Value , Learn how to copy data from one workbook to another using VBA macros in If you just Duration: 13:39 Posted: Jan 9, 2019 To copy value of same cell from multiple worksheets in the current workbook in Excel, you can use a formula based on the INDIRECT function and the ROW function to achieve the result. Firstly, you need to type cell reference that you want to copy into one blank cell in sheet4, such as: E1. And then you can run the following formula in another

VBA to Copy and Paste Rows if Condition is Met, The following macro examines the first 1,000 rows of Sheet1 and copies to Range("E1:E1000") ' Do 1000 rows If c = "yes" Then Source.Rows(c. I now want a specific cell B6 in the target worksheet to provide the search and I want to be able to go to another sheet and copy paste text and have it find a  Copy & Paste Over Existing Row / Column. This will copy row 1 and paste it into the existing row 5: This will copy column C and paste it into column E: Copy & Insert Row / Column. Instead you can insert the copied row or column and shift the existing rows or columns to make room.

Copying a Range of Cells to Another Sheet with Macro, VBA Code to Copy and Paste specific rows from one sheet to another to copy entire Duration: 10:44 Posted: May 13, 2017 How to create a Command Button to copy and paste data in Excel? Supposing you need to frequently copy a range of cells to other place after changing the data, the manually copy and paste method will be fussy and time-consuming.

Comments
  • Remove the Dim should work, This code needs to paste in the next available cell, as it stands, it would only paste in row "i"