Drag-down VBA function (many sheets) snag

rowbro

New Member
Joined
Dec 16, 2010
Messages
34
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:

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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,949
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.

Code:
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
 
Last edited:

Forum statistics

Threads
1,078,134
Messages
5,338,428
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top