Copy cell without border

[SparrowHawk]

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 _
    Destination:=Worksheets("Sheet2").Range("E5")

TIA
Ken

[Richie(UK)]

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("Sheet1").Range("B4").Copy
    Worksheets("Sheet2").Range("E5").PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

HTH

[tommy bak]

Hi
You could use pastespecial without borders

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

BR
Tommy Bak

[tommy bak]

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...
BR
Tommy Bak

[SparrowHawk]

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.

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

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

Thanks again,
Ken

[tommy bak]

Could this do it or have I misunderstood something ?

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

BR
Tommy Bak

[SparrowHawk]

Thanks ... it was a brain dead question 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

[tommy bak]

You're welcome, Ken

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

BR
Tommy Bak

[Zack Barresse]

Hi,

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.

[SparrowHawk]

Hi Zack,

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

Thanks,
Ken