Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    VBA to pastespecial borders only

    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.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    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
    Andrew Lockton, Chrysalis Design, Melbourne Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •