Code stops when copying and pasting in VBA

Question

I'm getting Application Defined or Object Defined Error on the line denoted with **. Is my syntax wrong?

Sub MonthLines()

Dim wkb As Workbook, shifts As Worksheet, inputs As Worksheet, StartDate As Range, EndDate As Range, InputDate As Range, numshifts As Integer, monthstart As Double
Dim monthend As Double, i As Double, n As Double, p As Double

Set wkb = Excel.Workbooks("Call Center Headcount Model v2.xlsm")
Set shifts = wkb.Worksheets("Shifting")
Set inputs = wkb.Worksheets("Inputs")

Set StartDate = wkb.Worksheets("Inputs").Range("C9")
Set EndDate = wkb.Worksheets("Inputs").Range("C10")
Set InputDate = wkb.Worksheets("Shifting").Range("B5")

monthstart = StartDate.Value
monthend = EndDate.Value

Application.Calculation = xlCalculationManual
p = 1

    For i = monthstart To monthend
        InputDate.Value = i
        Calculate
        numshifts = wkb.Worksheets("Shifting").Range("E5").Value
        For n = 21 To numshifts + 21
            With Sheets("Shifting")
            .Range(Cells(n, 1), Cells(n, 6)).Copy
            **wkb.Worksheets("Shift Output").Range(Cells(p, 1), Cells(p, 6)).PasteSpecial Paste:=xlPasteValues**
            End With
        Next n
    p = p + n
    
    Next i
    End Sub

That isn't the line I have the error on and the line after the WITH statement executes fine without them. — NoThanks, Sep 17 '20 at 02:41

K.Dᴀᴠɪs · Answer 1 · 2020-09-17T04:09:14.507

This is the prime example as to why it's always valuable to fully qualify all your range objects, even if at the time you don't see it as necessary.

Sure your first line below the With statement works, likely because you have that worksheet active. So VBA implicitly assigns whatever active worksheet you happened to have up at the time your runtime hits that line. This why why all of your range objects should be fully qualified, which includes the Cells() inside of your Range(). You did well qualifying Range() with the worksheet, but that qualification does not extend to the Cells() function in:
.Range(Cells(n, 1), Cells(n, 6)) or
wkb.Worksheets("Shift Output").Range(Cells(p, 1), Cells(p, 6))

Not tested, but this should work:

Dim wsShift As Worksheet, wsSO As Worksheet
Set wsShift = wkb.Worksheets("Shifting")
Set wsSO = wkb.Worksheets("Shift Output")

For i = monthstart To monthend

    InputDate.Value = i
    Calculate
    numshifts = wkb.Worksheets("Shifting").Range("E5").Value
    
    For n = 21 To numshifts + 21
        wsShift.Range(wsShift.Cells(n, 1), wsShift.Cells(n, 6)).Copy
        wsSO.Range(wsSO.Cells(p, 1), wsSO.Cells(p, 6)).PasteSpecial Paste:=xlPasteValues
    Next n
    
    p = p + n

Next i

Or if you really want to be proficient, drop the notoriously slow loop + .Copy/.Paste method and go with the array. Read once, right once instead of read/write whatever numShifts - 21 times is: (again, untested)

Dim dataArr() As Variant
dataArr = Workbooks("<ENTER WB>").Worksheets("Shifting").Range("A21:F" & numshifts + 21).Value
wkb.Worksheets("Shift Output").Range("A1:F" & numshifts + 21).Value = dataArr

Your first code will error because you don't assign the variable `p`. you also need to increment `p` within the `n loop`, to not paste over the same range. Your second is much better. — GMalc, Sep 17 '20 at 03:58
Oh yeah my intent wasn't very clear due to variable declarations showed no breaks between the declarations itself and the inner `For Loop`. I was only modifying the second loop and did not touch any of the code between the first / second. Thanks for pointing that out so I can make that clarification! — K.Dᴀᴠɪs, Sep 17 '20 at 04:03

Code stops when copying and pasting in VBA

1 Answers1