PDA

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



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

TIA
Ken

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

HTH

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



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


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

SparrowHawk
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.


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

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

Thanks again,
Ken

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


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

BR
Tommy Bak

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

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 :-)

BR
Tommy Bak

Zack Barresse
08-22-2004, 01:25 PM
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
08-23-2004, 02:10 AM
Hi Zack,

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

Thanks,
Ken