CODEWITHSUNDEEP
excelvbasyntaxcopy-paste
OMO
OMO

Reputation: 5

Paste Range Returning Syntax Error For Variable Range

I am trying to loop through a set of columns with a list of dates that vary in size and have numerous duplicates. I want to copy the column values from TSX-DeltaFind and paste them in TSX-CleanDate under the corresponding "Ticker" and to remove all duplicates once complete.

A) Why am I getting a syntax error on the paste line?
B) How can I delete all duplicates once these paste? 

Sub CleanDate()
    Dim BottomRow As Long
    Dim BottomRow2 As Long
    Dim TopRow As Long
    Dim col As Integer
    Dim Ticker As String
    Dim RngY As Range
    Dim originalRng As Integer

    With ThisWorkbook.Worksheets("TSX-DeltaFind")
        col = 4
        TopRow = 6

        For col = 4 To 3 + (2 * 26) Step 2
            Ticker = .Cells(TopRow - 1, col - 2).Value
            BottomRow = .Cells(.Rows.Count, col).End(xlUp).row
            originalRng = BottomRow - TopRow

            Worksheets("TSX-DeltaFind").Range(.Cells(TopRow, col), .Cells(BottomRow, col)).Copy
            Worksheets("TSX-CleanDate").Activate

            With ThisWorkbook.Worksheets("TSX-CleanDate")
                Set RngY = Worksheets("TSX-CleanDate").Range("A3:XDF3").Find(Ticker, lookat:=xlPart)
                BottomRow2 = .Cells(.Rows.Count, RngY.Column).End(xlUp).row

                Worksheets("TSX-CleanDate").Range(BottomRow2 + 1, RngY.Column + 2:originalRng + 4,RngY.Column + 2 ).PasteValues
            End With

            Worksheets("TSX-DeltaFind").Activate
        Next
    End With
End Sub

Upvotes: 0

Views: 38

Answers (1)

OwlsSleeping
OwlsSleeping

Reputation: 1570

For A) Your range argument should be something like range("a2:b2") rather than range(1,2:2,2)

You can use Cells to refer to them in the way you're trying. For example 

Worksheets("TSX-CleanDate").Range(BottomRow2 + 1, RngY.Column + 2:originalRng + 4,RngY.Column + 2 ).PasteValues

Could be

Worksheets("TSX-CleanDate").Range(Cells(BottomRow2 + 1, RngY.Column + 2),Cells(originalRng + 4,RngY.Column + 2)).PasteSpecial (xlPasteValues)

B) For duplicates, use range.removeduplicates

Upvotes: 1

Related Questions