Consulting

Results 1 to 10 of 10

Thread: Solved: Copy cell without border

  1. #1

    Question Solved: 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.

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

    TIA
    Ken

  2. #2
    VBAX Contributor Richie(UK)'s Avatar
    Joined
    May 2004
    Location
    UK
    Posts
    188
    Location
    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:[vba]Sub Macro1()

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

    End Sub[/vba]HTH

  3. #3
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Hi
    You could use pastespecial without borders

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

    BR
    Tommy Bak

  4. #4
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Discoved a funny thing here.

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

    [VBA]
    Selection.PasteSpecial Paste:=xlAllExceptBorders, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    [/VBA]

    Using the recorder in XP gave this code which works in both xl2000 and xlXP
    [VBA]
    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    [/VBA]

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

  5. #5
    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

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    Could this do it or have I misunderstood something ?

    [VBA]
    Me.Range("B4:M24").ClearContents

    [/VBA]
    BR
    Tommy Bak

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

  8. #8
    VBAX Regular
    Joined
    Jun 2004
    Location
    Denmark
    Posts
    58
    Location
    You're welcome, Ken

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

    BR
    Tommy Bak

  9. #9
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    Hi,

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


    [vba]Sheets("Sheet2").Range("E5").Value = Sheets("Sheet1").Range("B4").Value[/vba]
    Just as another option. Take care.

  10. #10

    Talking

    Hi Zack,

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

    Thanks,
    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •