Reputation: 117
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
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