I am trying to copy the format of first row to rest of the rows in my range

Question

I have created 2 funcs, but both give Run-time error '1004': Application-defined or object-defined error. Can you tell me what is the error here.

Sub test()
    Dim RowCount, ColumnCount As Integer
    Dim sRangeSt, sRangeEn, tRangeSt, tRangeEn As String
    Dim tmpRange As Range

        RowCount = Range("main").Rows.Count
        ColumnCount = Range("main").Columns.Count

        'prepare range start and end cell-addresses
        sRangeSt = Range("main").End(xlToLeft).Offset(1, 0).Address
        sRangeEn = Range("main").End(xlToLeft).Offset(1, 0).End(xlToRight).Address
        tRangeSt = Range("main").End(xlToLeft).Offset(2, 0).Address
        tRangeEn = Range("main").End(xlToLeft).Offset(RowCount - 1, ColumnCount - 1).Address

        'Perform style copy
        Range(sRangeSt & ":" & sRangeEn).Copy
        'Worksheets("Report").Select
        Worksheets("Report").Range(tRangeSt & ":" & tRangeEn).PasteSpecial xlPasteFormats
End Sub


Sub test2()
    Dim sRangeSt, sRangeEn, tRangeSt, tRangeEn As String
    sRangeSt = Range("main").End(xlToLeft).Offset(1, 0).Address
    sRangeEn = Range("main").End(xlToLeft).Offset(1, 0).End(xlToRight).Address

    'Perform style copy
    Range(sRangeSt & ":" & sRangeEn).Copy

    For Each Row In Range("main").Rows
        Range(Row.Address).PasteSpecial xmlPasteFormts
    Next Row
End Sub

First block gives error at the last line. I tried by reducing the row-count of target rows, and it ran fine for excel range 4096 rows, but failed for ranges of greater row-count with the above error.

@L42 - Address is '=Report!$A$14:$P$12458', and scope is 'workbook'. — SUMIT CHATTERJEE, Jan 08 '14 at 12:50
I tried your code and it is working. maybe you should improve your variable declarations so you would always get the result you want. See [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) for some guidance. — L42, Jan 09 '14 at 01:07

score 0 · Answer 1 · answered Jan 08 '14 at 11:28

0

You are getting that error because your range is not fully qualified. Just by enclosing the code in With End With will not qualify the range. Add a . (DOT) before every range. For example

With Sheets("Report")
    RowCount = Range("main").Rows.Count
    ColumnCount = Range("main").Columns.Count

becomes

With Sheets("Report")
    RowCount = .Range("main").Rows.Count
    ColumnCount = .Range("main").Columns.Count

Similarly for the rest

answered Jan 08 '14 at 11:28

Siddharth Rout

147,039
17
206
250

Thanks for your response - Siddharta. Actually, I `m not using 'With - EndWith' this block here. as 'main' is a workbook-scoped range. We can ignore that.. I`l update my code. The error is occuring in the last line... It pastes fine till 4096 rows, but in the next row it gives ' Application-defined or object-defined error'. – SUMIT CHATTERJEE Jan 08 '14 at 11:32

I am trying to copy the format of first row to rest of the rows in my range

1 Answers1