CODEWITHSUNDEEP
vbaexcel
zion
zion

Reputation: 421

Application-defined or object-defined error VBA Copy between Sheets

I have just started working on VBA, and I'm stuck with this error while copying data between different sheets.

The requirement was to find specific block of data points and copy them on a separate sheet.

Variable like FirstRowArrayCol1 Contain the position of Data points, whereas i denotes the column numbeer.

The error is coming in this line

Set rngDest = wksDest.Range(Cells(i, firstPos))

Please check the below code. Greatly Appreciate any help, thanks.

Set wksSource = ActiveWorkbook.Sheets("Sheet1")
Set wksDest = ActiveWorkbook.Sheets("Sheet2")
        For Each firstPos In FirstRowArrayCol1
            If firstPos = 0 Then
                Exit For
            End If
            For Each secondPos In SecondRowArrayCol1
                If secondPos = 0 Then
                    Exit For
                End If
                Diff = Abs(firstPos - secondPos)
                If Diff > 0 And Diff <= 5 Then
                    Debug.Print (column3 & firstPos & "," & column3 & secondPos & "," & column4 & firstPos & "," & column4 & secondPos)
                    'Copy Data
                    Set rngSource = wksSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos))
                    rngSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos)).Select
                    rngSource.Copy
                    'Paste Data Values
                    Set rngDest = wksDest.Range(Cells(i, firstPos))
                    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                End If
            Next secondPos
            For Each secondPos In SecondRowArrayCol2
                If secondPos = 0 Then
                    Exit For
                End If
                Diff = Abs(firstPos - secondPos)
                If Diff > 0 And Diff <= 5 Then
                    Debug.Print (column3 & firstPos & "," & column3 & secondPos & "," & column4 & firstPos & "," & column4 & secondPos)
                    'Copy Data
                    Set rngSource = wksSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos))
                    rngSource.Range(Cells(i, firstPos), Cells(i + 1, secondPos)).Select
                    rngSource.Copy
                    'Paste Data Values
                    Set rngDest = wksDest.Range(Cells(i, firstPos))
                    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

                End If
            Next secondPos
        Next firstPos

Upvotes: 1

Views: 1981

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57673

The Worksheet.Range Property accepts 2 cells like 

Set rngDest = wksDest.Range(Cells(i, firstPos), Cells(i, firstPos))

or one address

Set rngDest = wksDest.Range(Cells(i, firstPos).Address)

but not 1 cells

Set rngDest = wksDest.Range(Cells(i, firstPos)) 'not valid

because therefore you can use Cells directly

Set rngDest = wksDest.Cells(i, firstPos)

Upvotes: 3

Christian T
Christian T

Reputation: 128

Change Set rngDest = wksDest.Range(Cells(i, firstPos)) to:

Set rngDest = wksDest.Cells(i, firstPos)

Upvotes: 1

Related Questions