Copy/Paste Borders only, not number formats, fonts, etc.

[BBDudley]

In Excel 2003, is there an easy way to paste borders into other cells on-the-fly without affecting other formatting settings (Number format, Font, Cell Shading, etc) in the destination cells?

In short, I need PasteSpecial|Borders Only to exist -- the exact opposite of PasteSpecial|AllExceptBorders.

More detail:

• I have pivot tables with Borders set for subtotals and totals.
  
• Therefore, when I update the tables with new data, the location of the borders change.
  
• After updating the pivot tables, I would like to copy and paste these borders (only) from the updated pivot table onto to all data and formulas to the left of it. (For example, Copy All of Column C and Paste Special, [some Borders-only function] into Columns J:AE)
• The columns to the left of the table are all formulas with Formats that need to remain unchanged: Number (dates, currency), Font (style, size), Alignment, Patterns (shading). All I want applied to them are the updated Borders.
  

How do I get the borders and only the borders copied onto the data without changing any other cell formatting?

Thank you for your assistance!

 

[JLGWhiz]

I had never tried that before and after doing a little reading in the help files and looking at the options in the paste special dialog box and VBA attributes, it seems that you have two options. Either copy the borders from an otherwise empty cell, or use the Format Cells facility to put your borders where you want them. I don't believe you can do what you want with a copy and paste as long as there is data in the source cell.

 

[JLGWhiz]

Well, there is one way. Assuming cell B3 has borders, You could use code something like this:

Code:
Range("B3").Copy Range("D5")
Range("D5").ClearContents
Code: