CODEWITHSUNDEEP
vbaexcelpaste
Jack
Jack

Reputation: 3

Paste Values From One Worksheet To Another If X = True

I'm trying to make a macro that, if column F in the fifth worksheet is = 3, pastes the values to the left of the 3 (columns A through E) into another sheet (sheet 1). I can't seem to even get started. When I run the macro nothing happens. I'm sure I've made a bunch of dumb mistakes.

Thanks in advance for your help!

Jack

Sub Movevalues()
        Dim q As Integer, w As Integer
    w = 7
    For q = 1 To 1000
        If ActiveWorkbook.Worksheets("Taxable Accounts Import").Cells(q, 6).Value = 3 Then
            Range(Cells(q, 1), Cells(q, 5)).Select
            Selection.Copy
            Worksheets(1).Select
            Range(22, w).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Worksheets(5).Select
            w = w + 1
        End If
    Next q
End Sub

Upvotes: 0

Views: 859

Answers (2)

Dan Wagner
Dan Wagner

Reputation: 2713

I think the issue here is that you're copying 5 cells in a row from Worksheets(5), but only incrementing w by 1 on each for loop. If the goal really is to add to the same row on Worksheets(1), you would need to increment w by 5 instead... which is a nice, easy fix haha:

w = w + 5

That being said, you're looping 1000 times, which means there are potentially 1000 matches, which would populate 1000 columns (or 5000 columns if my by-5-correction is accurate). That's a lot! If your intention was to instead start at row 7, column 22, and increment rows from there, I might use the following strategy. (Heavily commented to explain what's going on...)

Option Explicit
Sub MoveValuesRev2()

Dim q As Long, w As Long
Dim TAI As Worksheet, SheetOne As Worksheet, _
    SheetFive As Worksheet
Dim Source As Range, Target As Range

'set references up-front
w = 7
Set TAI = ThisWorkbook.Worksheets("Taxable Accounts Import")
Set SheetOne = ThisWorkbook.Worksheets(1)
Set SheetFive = ThisWorkbook.Worksheets(5)

'loop through the cells in question
For q = 1 To 1000
    If TAI.Cells(q, 6).Value = 3 Then
        'store the left-of-the-found-value-3 cells in a range
        With SheetFive
            Set Source = .Range(.Cells(q, 1), .Cells(q, 5))
        End With
        'set the target range in row w, col 22
        With SheetOne
            Set Target = .Cells(w, 22)
        End With
        'the next two lines are the copy and paste step
        Source.Copy
        Target.PasteSpecial (xlPasteValues)
        'increment w
        w = w + 1
    End If
Next q

End Sub

Upvotes: 1

David Zemens
David Zemens

Reputation: 53663

I think it will be easier to use some variables rather than explicit references. One thing this will make easier is that you don't need to keep "Selecting" the sheets back-and-forth.

I will try to comment what I am doing so that it is understandable for you.

Untested, so let me know if there is any trouble with it.

Sub Movevalues()
    Dim q As Integer, w As Integer
    Dim wsSource as Worksheet      'represents the SOURCE worksheet
    Dim wsDest as Worksheet        'represents the DESTINATION worksheet
    Dim copyRange as Range         'represents the range we want to COPY
    Dim destRange as Range         'represents the destination range

    'Initialize some variables
    w = 7
    Set wsSource = ActiveWorkbook.Worksheets("Taxable Accounts Import")
    Set wsDest = ActiveWorkbook.Worksheets(1)

    For q = 1 To 1000
        With wsSource
        If .Cells(q, 6).Value = 3 Then

            'Define the range to be "copied"
            Set copyRange = .Range(.Cells(q,1), .Cells(q, 5))

            'Define the destination range using the Resize method:
            Set destRange = wsDest.Range(22, w).Resize(1,copyRange.Columns.Count)

            'Here, we don't need to select or even "copy" anything, we can write directly
            '  to one range, from another.
            destRange.Value =  copyRange.Value

            'ensure that w identifies the next column and does not overwrite the values
            ' that we just transferred, above.
            w = w + copyRange.Columns.Count
        End If
    Next q
End Sub

Note: this assumes that it is your intention to copy rows of data and put it all in a single row on the destination sheet, as per your example (which uses Row 22, column w as the paste destination).

Upvotes: 1

Related Questions