none
Fill columns in multiple worksheets RRS feed

  • Question

  • Hello, I have this code which works well if all the sheets have rows that are empty and therefore the fill-down works BUT if one sheet has nothing to fill down (i.e. the last row is complete in all columns), then the macro stops at that sheet wiith the message: Run Time error 1004. AutoFill method of Range class failed.

    I am looking for code to enable the macro to skip sheets that have complete rows.

    Many thanks in advance,

    CC

    Sub FillColumns()

        Dim r1 As Long, r2 As Long, I As Long
        Dim ws As Worksheet
        Dim wsCounter As Long
        
        
        For wsCount = 5 To ActiveWorkbook.Worksheets.Count
            Set ws = ActiveWorkbook.Worksheets(wsCount)
            With ws
                r1 = .Range("A" & .Rows.Count).End(xlUp).Row
                r2 = .Range("L" & .Rows.Count).End(xlUp).Row
                .Range(.Cells(r2, 12), .Cells(r2, 19)).AutoFill .Range(.Cells(r2, 12), .Cells(r1, 19))
            End With
        Next

    End Sub

    Sunday, January 20, 2019 7:17 PM

Answers

  • You could use

    Sub FillColumns()

        Dim r1 As Long, r2 As Long, I As Long
        Dim ws As Worksheet
        Dim wsCounter As Long

        For wsCount = 5 To ActiveWorkbook.Worksheets.Count
            Set ws = ActiveWorkbook.Worksheets(wsCount)
            With ws
                r1 = .Range("A" & .Rows.Count).End(xlUp).Row
                r2 = .Range("L" & .Rows.Count).End(xlUp).Row
                If r1 <> r2 Then
                    .Range(.Cells(r2, 12), .Cells(r2, 19)).AutoFill .Range(.Cells(r2, 12), .Cells(r1, 19))
                End If
            End With
        Next

    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by czap1 Sunday, January 20, 2019 10:06 PM
    Sunday, January 20, 2019 8:33 PM

All replies

  • You could use

    Sub FillColumns()

        Dim r1 As Long, r2 As Long, I As Long
        Dim ws As Worksheet
        Dim wsCounter As Long

        For wsCount = 5 To ActiveWorkbook.Worksheets.Count
            Set ws = ActiveWorkbook.Worksheets(wsCount)
            With ws
                r1 = .Range("A" & .Rows.Count).End(xlUp).Row
                r2 = .Range("L" & .Rows.Count).End(xlUp).Row
                If r1 <> r2 Then
                    .Range(.Cells(r2, 12), .Cells(r2, 19)).AutoFill .Range(.Cells(r2, 12), .Cells(r1, 19))
                End If
            End With
        Next

    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by czap1 Sunday, January 20, 2019 10:06 PM
    Sunday, January 20, 2019 8:33 PM
  • perfect. Thank you for your help.
    Sunday, January 20, 2019 10:06 PM