0

I wrote a code to copy and paste a list of persons 12 times each. My code works perfectly when I pass a range that looks like this

Worksheets("DPs").Range("A2:D2").Copy

That will copy the first person on the list all the way down on the correct worksheet.

However when I try pass a range like this it gives me an object-defined error:

Worksheets("DPs").Range(Cells(i + 1, 1), Cells(i + 1, 4)).Copy

Looking online I can supposedly pass a range with the cells method but it won't work.

Any ideas? Here is my complete code:

Sub addDpRep()

Dim x As Integer
Dim y As Integer

x = Worksheets("DPs").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count

For i = 1 To x

y = Worksheets("FOR FILE").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count

    Worksheets("DPs").Range(Cells(i + 1, 1), Cells(i + 1, 4)).Copy
    For j = 1 To 12
        Worksheets("FOR FILE").Cells(y + j, 2).PasteSpecial Paste:=xlPasteValues
    Next j

Next i


End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
thedataguydj
  • 1
  • You need to qualify the worksheet when calling `Cells` – BigBen Feb 04 '21 at 03:19
  • 1
    Default scope for Range/Cells/etc https://stackoverflow.com/questions/28439376/what-is-the-default-scope-of-worksheets-and-cells-and-range/28439984#28439984 – Tim Williams Feb 04 '21 at 03:32
  • I was trying different options and actually got a work around: `Dim row As Integer``row = i + 1``Worksheets("DPs").Range("A" & row & ":D" & row).Copy` – thedataguydj Feb 04 '21 at 04:05
  • What both commenters above say is that `Worksheets("DPs").Range` is on the "DPs" tab but `Cells(i + 1, 1)` is on the Activesheet.. Therefore your code will work when the DBs tab is active and fail when another sheet is active. Setting the range using cells is the better way but you should deploy it like this: `With Worksheets("DPs"): .Range(.Cells(i + 1, 1), .Cells(i + 1, 4)).Copy: End With` – Variatus Feb 04 '21 at 04:06

0 Answers0