Copy cell without border

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.

Code:

---

Worksheets("Sheet1").Range("B4").Copy _
    Destination:=Worksheets("Sheet2").Range("E5")

---

TIA
Ken

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:

Code:

---

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

---

HTH

Hi
You could use pastespecial without borders

Code:

---

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

---

BR
Tommy Bak

Discoved a funny thing here.

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

Code:

---

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

Code:

---

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

---

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

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.

Code:

---

Me.Range("B4:M24").Clear

---

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

Thanks again,
Ken

Could this do it or have I misunderstood something ?

Code:

---

Me.Range("B4:M24").ClearContents

---

BR
Tommy Bak

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,
Ken

You're welcome, Ken

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

BR
Tommy Bak

Hi,

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

Code:

---

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

---

Just as another option. Take care. :)

Hi Zack,

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

Thanks,
Ken