Robillard
Robillard

Reputation: 117

VBA copy formatting from a row to several rows

Alright so I am really close to getting this but I am just trying to make it work better. I want to copy row 2 formatting that goes until like Column H. The data only goes until Column H. So my code copies ONLY row 2 until Column H. But when it goes to paste, it highlights the whole sheet besides row 1 and it looks like it copies the formatting across the whole thing. It is not really an issue but I would rather know how to make it paste only in the rows and columns I want for future reference. I only want it going to cells that have data in it basically. Thanks for the help in advance!

    Range("A2", Cells(2, Columns.Count).End(xlToLeft)).COPY
    Range("A2", Cells(Range("A" & Rows.Count).End(xlDown).Row)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

Upvotes: 3

Views: 17001

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Try this:

Dim rngCopy As Range, rngPaste As Range


With ActiveSheet
    Set rngCopy = .Range(.Range("A2"), .Cells(2, Columns.Count).End(xlToLeft))

    Set rngPaste = .Range(.Range("A2"), _
                       .Cells(Rows.Count, 1).End(xlUp)).Resize( , rngCopy.Columns.Count)

End With

rngCopy.Copy
rngPaste.PasteSpecial  Paste:=xlPasteFormats
Application.CutCopyMode = False

Upvotes: 7

Related Questions