Copy and Paste Loop in VBA based on cell values

Question

I am trying to create some code that looks through a range of cells and will copy and paste the cells that meet a specific parameter to a different location in the workbook.

I would like to copy anything with the letter L from "sheet5" and copy a specific range to "sheet1"

I must have something wrong with the loop part of the code because only the top of the cell range is being copied. I would like the pasting to start at row 5 and continue moving downward. Does this mean I correctly put the IRow = IRow + 1 below the paste function?

Sub Paste_Value_Test()

Dim c As Range
Dim IRow As Long
Dim rDestination As Excel.Range

Application.ScreenUpdating = False
Sheets("sheet5").Activate
For Each c In Sheets("sheet5").Range("b2", Range("N65536").End(xlUp))
    If c.Value = "L" Then
        Sheets("sheet5").Cells(c.Row, 2).Copy

        Set rDestination = Worksheets("sheet5").Cells(5 + IRow, 12)

        rDestination.Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False

        IRow = IRow + 1

    End If
Next c

End Sub

I really appreciate any help on this. I'm relatively new to VBA and am going to start seriously digging in.

So the "L" can be anywhere in Columns B:N? And when found you want to copy the value from Col 2 of that row? — Siddharth Rout
– Siddharth Rout, Commented Jan 29, 2014 at 20:08

Siddharth Rout · Accepted Answer · 2014-01-29 20:21:56Z

3

Is this what you are trying by any chance? I have commented the code so you shouldn't have any problem understanding it.

Sub Paste_Value_Test()
    Dim c As Range
    Dim IRow As Long, lastrow As Long
    Dim rSource As Range
    Dim wsI As Worksheet, wsO As Worksheet

    On Error GoTo Whoa

    '~~> Sheet Where "L" needs to be checked
    Set wsI = ThisWorkbook.Sheets("Sheet5")
    '~~> Output sheet
    Set wsO = ThisWorkbook.Sheets("Sheet1")

    Application.ScreenUpdating = False

    With wsI
        '~~> Find Last Row which has data in Col B to N
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Columns("B:N").Find(What:="*", _
                          After:=.Range("B1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lastrow = 1
        End If

        '~~> Set you input range
        Set rSource = .Range("B2:N" & lastrow)

        '~~> Search for the cell which has "L" and then copy it across to sheet1
        For Each c In rSource
            If c.Value = "L" Then
                .Cells(c.Row, 2).Copy
                wsO.Cells(5 + IRow, 12).PasteSpecial Paste:=xlPasteValues

                IRow = IRow + 1
            End If
        Next
    End With

LetsContinue:
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

answered Jan 29, 2014 at 20:21

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dutchballa Over a year ago

I really appreciate the help on this. That worked perfectly for what I wanted to do. My explanation was confusing because I grabbed code from another post that I did not edit enough for my scenario. I am planning on going through a full VBA tutorial, but if you have any sources that are useful for learning data manipulation in VBA, that would be very helpful.

Siddharth Rout Over a year ago

Glad it helped :) What kind of data manipulation? Stackoverflow has lot of examples on data manipulation. Then there is google which can be of great help if you know how to search :)

dutchballa Over a year ago

Got it. I think that is the best bet. Plan is to go through a tutorial to make sure I can debug and have a general understanding of VBA. Then going to learn things on a project basis. This is a great forum. Thanks a ton for the help.

Collectives™ on Stack Overflow

Copy and Paste Loop in VBA based on cell values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related