Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying formatting in VBA

    Hi all.

    I was recently shown that you could copy data from one s/sheet to another using code like:

    Sheets(1).range("A1:A3").value=Sheets(2).range("A1 :A3").value

    but is there an easy way to copy formatting without using copy and pastespecial? I looked, but it would appear that I would have to copy borders, then font, then cells etc, and it seems rather long winded.

    Thanks.

    Stuart

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formatting in VBA

    I don't know of any way other than copying one property at a time.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formatting in VBA

    Stuart,

    You say can't use pasteSpecial etc, is there a reason why not? It seems like a nice way to do what you want.

    I suspect if Legare doesnn't know a way otherwise, there is not a way.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formatting in VBA

    It wasn't so much a case of not being able to use it, as not wanting to use it. Swapping between different workbooks to get and paste data seems very inefficient - I'd had problems in the past (in 97) with doing operations like

    book.sheet.range.copy

    and took to doing it the long way (which, incidentally, is the way it gets done if you record a macro) of

    book.activate
    sheet.activate
    range.copy
    other book.activate
    range.pastespecial

    Perhaps the problems aren't there now, and I can safely do this. I'll have a look. Thanks for the feedback.

    Stuart

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying formatting in VBA

    Other than getting the syntax right, I don't know of any problems using Copy and PasteSpecial to do this. It has worked fine for me.
    Legare Coleman

Posting Permissions

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