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")

    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!