PDA

View Full Version : Paste column format only through iteration



shettyrish
01-23-2018, 10:46 PM
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

paulked
01-23-2018, 11:27 PM
Dim x As Long
Columns(2).Copy
For x = 3 To LastCol
Columns(x).PasteSpecial Paste:=xlPasteFormats
Next

shettyrish
01-23-2018, 11:36 PM
Thanks! It works Perfectly :) I had figured out an indirect method but this is so much better.

This was my method :
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

mancubus
01-23-2018, 11:47 PM
With ThisWorkbook.Sheets(DestName)
LastRow = .Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = .Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
.Range(.Cells(1, 2), .Cells(LastRow, 2)).Copy
.Range(.Cells(1, 3), .Cells(LastRow, LastCol)).PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False

shettyrish
01-24-2018, 12:05 AM
Hi mancubus Thank you for answering my query. Even though this solution works, it is a slightly lengthier solution than the one paulked gave.