Drag-down VBA function (many sheets) snag

[rowbro]

Hi all,

I am experiencing the below issue with my VBA drag-down code across many sheets. It works for the first sheet then gets stuck - and help as to why would be appreciated. Here is the error message: https://ibb.co/j4rB86H

Here is the code:

Sub DragDown()

    Dim LR As Integer
    Dim LR1 As Integer
    Dim i As Integer
    Dim dragSheets As Variant
        
    Application.ScreenUpdating = False
    
    
    Sheets("UPDATE").Select
    j = Cells(2, "H").Value
    
    dragSheets = Array("NAMTB", "GC21", "GC22", "GC23", "GC24", "GC25", "GC27", "GC30", "GC32", "GC35", "GC37", "GC40", "GC43", "GC45", "GC50", "GI22", "GI25", "GI29", "GI33", "GI36", "Relatives", "TB Relatives", "Average Spreads", "Generic", "Slope", "USDZAR", "MD")
    
    For i = 0 To UBound(dragSheets)


        Sheets(dragSheets(i)).Select
        LR = Sheets(dragSheets(i)).Cells(Rows.Count, 1).End(xlUp).Row
        LRP = LR + j
        Range("A" & LR & ":ZZ" & LR).Select
        Selection.AutoFill Destination:=Range("A" & LR & ":ZZ" & LRP), Type:=xlFillDefault
        
    Next i
    
    Sheets("UPDATE").Select
    
    MsgBox ("Drag Down Complete")
    
    Application.ScreenUpdating = True
    
End Sub

Note: Sheets("UPDATE").Select j = Cells(2, "H").Value is the number of cells to drag down, generally 1.

It should cycle through all the sheets listed under the array.

Can anyone see what I have got wrong?

 

[John_w]

Your code looks OK - which line causes the error? Click Debug on the error message and the errant line is highlighted in yellow. (BTW it's easier for people helping you to put the complete error message in your post).

Try this version, which doesn't select sheets or cells. I've also changed your Integers to Longs, because the maximum value of an Integer is 32767, so if the value of LR or LR+j (i.e. the range of rows to auto fill) is greater than 32767 an error will occur. Longs have a maximum value of over 2 billion.

Sub DragDown()

    Dim LR As Long
    Dim i As Long, j As Long
    Dim dragSheets As Variant
        
    Application.ScreenUpdating = False
    
    j = Worksheets("UPDATE").Range("H2").Value
    
    dragSheets = Array("NAMTB", "GC21", "GC22", "GC23", "GC24", "GC25", "GC27", "GC30", "GC32", "GC35", "GC37", "GC40", "GC43", "GC45", "GC50", "GI22", "GI25", "GI29", "GI33", "GI36", "Relatives", "TB Relatives", "Average Spreads", "Generic", "Slope", "USDZAR", "MD")
    
    For i = 0 To UBound(dragSheets)
        With Worksheets(dragSheets(i))
            LR = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A" & LR & ":ZZ" & LR).AutoFill Destination:=.Range("A" & LR & ":ZZ" & LR + j), Type:=xlFillDefault
        End With
    Next i
    
    Worksheets("UPDATE").Activate
    
    MsgBox "Drag Down Complete"
    
    Application.ScreenUpdating = True
    
End Sub