Paste only column format through iteration

  • Hi, I have a code that copies data from one workbook to another. Now, the 2nd column in my destination workbook has a certain color format that I need to be applied till the last column with data in it.


    This is my code snippet to find the last non-empty column :


    Dim rLastCell As Range
    Set ws = ThisWorkbook.Sheets(DestName)


    Set rLastCell = ws.Cells.Find(What:="*", After:=ws.Cells(1, 1), LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)


    Dim LastCol As Long


    LastCol = rLastCell.Column


    MsgBox LastCol


    So, now I want to iterate and paste the format from the 2nd column to all the columns until LastCol.


    Any help would be appreciated. Thank you

  • I figured it out


    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

    MsgBox "Last Column: " & lCol

    Dim CopyRng As Range
    Set CopyRng = Wb.Sheets(DestName).Range("B1:B57")
    CopyRng.Copy

    For j = 2 To lCol

    Wb.Sheets(DestName).Cells(1, j).PasteSpecial xlPasteFormats

    Next j

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!