VBA/Macro Copy Specified Row Format Down to Last Row with Data

[MagzP]

VBA/Macro Copy Specified Row Format Down to Last Row with Data

Can't figure out how to get this to work.

' Copy Format Down
Rows("16:16").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Need to make sure it goes to last row with data, not to ROW 1048576. When I try to do this with Columns it will apply the format but does not stop at last column with data.

 

[tonyyy]

MagzP,

If you want to stop the formatting at the last row and last column with data, then you have to tell it so, for example...

Sub test4()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim LastColumn As Long
    
    LastRow = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    LastColumn = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        
    ' Copy Format Down
    Range(Cells(16, 1), Cells(16, LastColumn)).Copy
    Range(Cells(16, 1), Cells(16, LastColumn)).Resize(LastRow - 15, LastColumn). _
        PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy

 

[MagzP]

Thanks tonyyy,
That worked. Maybe you can help with the other format I need to apply.
I want to copy format of G:K and apply it all the way to last column. So repeating a 5 column format over and over.

Much Appreciated,
MagzP

 

[tonyyy]

You're welcome, MagzP. Glad that worked out.

As for your other format, you might give this a try...

Sub test5()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim i As Long, j As Long
    
    i = 7
    j = 11
    LastRow = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    LastColumn = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        
    ' Copy Format Across
    Range(Cells(1, i), Cells(LastRow, j)).Copy
    Do Until i >= LastColumn
        i = i + 5
        j = j + 5
        Range(Cells(1, i), Cells(LastRow, j)). _
            PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Loop
        
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[prsnthkrishna]

Hi Guys, I want to apply the format painter from A1 to last cell in header column. The count of cells vary in reports. Can anyone help here with vba..

 

[Yna26]

MagzP,

If you want to stop the formatting at the last row and last column with data, then you have to tell it so, for example...

Sub test4()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim LastColumn As Long
    
    LastRow = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    LastColumn = ActiveSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        
    ' Copy Format Down
    Range(Cells(16, 1), Cells(16, LastColumn)).Copy
    Range(Cells(16, 1), Cells(16, LastColumn)).Resize(LastRow - 15, LastColumn). _
        PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy

Hello Tonyyy, I am using the same above code for my purpose however I'm trying to make it read the last row and last column. How can i do that without giving it a specific row number? Rows in my worksheet are constantly increasing as I am importing data from other workbooks almost daily hence I want these new imported rows to get the same format as the previous based on last row. Thanks!