View Full Version : [SOLVED:] Copy cell without border

08-22-2004, 09:00 AM
I know how simple this question is but I just cannot find the solution anywhere on the net or in the help files.

I have data in a cell on a sheet in Excel with a border set around the cell. I want to copy the data to another sheet but without the border. The following code works for the data but it also copies the border.

Worksheets("Sheet1").Range("B4").Copy _


08-22-2004, 09:29 AM
Hi Ken,

Welcome to the board.

OK, if you were doing this manually a simple copy and paste would have the same effect, right? It'd copy the border too. So, you'd actually do a copy and then PasteSpecial and choose values only. Voila, no borders.

To get the syntax for doing this (and for many other areas where you may need a quick reminder) just use the good old Macro Recorder and then examine the code. A little editing of the recorded code gives us:

Sub Macro1()
Worksheets("Sheet2").Range("E5").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub


tommy bak
08-22-2004, 09:45 AM
You could use pastespecial without borders

Sheets("Sheet2").Range("E5").PasteSpecial Paste:=xlPasteAllExceptBorders

Tommy Bak

tommy bak
08-22-2004, 09:55 AM
Discoved a funny thing here.

Using the macrorecorder in xl2000 gave me this code that does NOT work.

Selection.PasteSpecial Paste:=xlAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Using the recorder in XP gave this code which works in both xl2000 and xlXP

Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

I have never seen this error before...
Tommy Bak

08-22-2004, 10:29 AM
Thanks guys ... I knew it was a pretty simple thing ... However, this brings me to my next question - if I want to clear the contents of those new cells, using something along this line clears the data but also the borders as well.


What if I want to leave the borders as they are?

Thanks again,

tommy bak
08-22-2004, 10:35 AM
Could this do it or have I misunderstood something ?


Tommy Bak

08-22-2004, 10:53 AM
Thanks ... it was a brain dead question :roll: but that is indeed the way I wanted things to work. Sigh. I suppose the only excuse I can offer is that we were in the path of the eye of hurricane Charley last week and my concentration seems to have left me ever since.

I appreciate the quick response however. I had not realized that I could record a macro and see the code before it was mentioned in a reply. That makes it much simpler ... as long as there are menu commands available to do what is needed.

Thank you again,

tommy bak
08-22-2004, 11:06 AM
You're welcome, Ken

You don't have to excuse yourself, you do not have patent on easy questions :-)

Tommy Bak

Zack Barresse
08-22-2004, 01:25 PM

Although I see you have a solution, here is another that you can use without using the copy command ...

Sheets("Sheet2").Range("E5").Value = Sheets("Sheet1").Range("B4").Value

Just as another option. Take care. :)

08-23-2004, 02:10 AM
Hi Zack,

Wow ... that's a really clean and simple way to do the job.