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

Question

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

I think you have your answer below but as a side note: I wouldn't use array in this instance as your are working directly with the range anyway. So would suggest to use the Range — Zac
– Zac, Commented Mar 23, 2018 at 10:51

Pᴇʜ · Accepted Answer · 2018-03-23 10:54:13Z

3

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)

edited Mar 23, 2018 at 10:54

answered Mar 23, 2018 at 10:45

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

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

Comments

Christian T · Accepted Answer · 2018-03-23 10:41:11Z

1

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

Set rngDest = wksDest.Cells(i, firstPos)

answered Mar 23, 2018 at 10:41

Christian T

1282 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related