VBA to pastespecial borders only

Topic

New Reply

I have a row with borders from COL B to COL W, row 40. I want to copy the borders of row 40 to a range B41:W60. There are numbers with percentage formats and general number formats in the range (B41:W60) that i don’t want to be changed by the number formatting in row 40. I just want to copy the borders to that range. Is that possible using pastespecial or vba code? Thanks for the help.

Reply | Quote

Replies

There is a paste option that is AllExceptBorders so we could do this with a bit of a workaround.

The principle is:
1. Take a copy of everything (in the cells you want to apply borders to) and paste this to a temp range
2. Copy all formatting to those cells
3. Reimport all formatting except the borders from the temp range
4. Clean up the temp range.

I’m assuming that rows 200+ onwards are not currently populated.

Code:

Sub Macro1()
  Range("B41:W60").Copy
  Range("B201").Select
  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("B40:W40").Copy
  Range("B41:W60").Select
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("B201:W260").Copy
  Range("B41").Select
  Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("B201:W260").Clear
End Sub

Reply | Quote