Copy Paste Loop ends before copy/paste is finished

Hello,

I am trying to copy the values from a pivot table into the adjacent column. My current code works fine, but it ends the loop at row 35. I cannot figure out why. The only reason the extra "Do" loop is in there is because I thought it could help... it doesn't.

Sub Copy_and_Paste_Loop_from_Pivot_Table()

Do

Dim i As Variant

For i = 4 To Worksheets("Detail by Line Pivot").Range("B4").Value

    Cells(i, 2).Select
    Selection.Copy
    Cells(i, 2).Offset(1, 1).Select
      
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, skipblanks:=False, Transpose:=False
    
Next i

Loop Until IsEmpty(ActiveCell.Offset(0, 1))

End Sub

Thank you!

Hi brgr4u.

Probaby Worksheets("Detail by Line Pivot").Range("B4").Value
is 35?

It is still ending the loop at row 35. Additionally, the cells that need to be copied in rows 35 and 36 both have a value of 35.

You guessed it. Do you know of any way to work around this?

Originally Posted by RHCPgergo

Not sure if this is what you want, but here's my try.

Sub CopyRange()

Dim RngToCopy As Range 'range to copy
Dim Lr As Long 'last row

With Worksheets("Detail by Line Pivot")
    Lr = .Range("B" & Rows.Count).End(xlUp).Row
    Set RngToCopy = .Range("B4:B" & Lr)
    RngToCopy.Copy
    RngToCopy.Offset(1, 1).PasteSpecial xlPasteFormulas
End With

End Sub

oops. Edited to paste special.

Yes! That is 95% of what I want, I can take the rest from here. Thank you!!

Thank you arlu1201!