"Copying" cell format in VBA

  • Hi,


    I instruct VBA to insert a value from a cell (say 1,1) to another one (say 1, 3) using this notation:


    Code
    1. Cells(1, 3).Value = Cells(1, 1).Value


    This works without problems. However, I would also like to copy cell format (all kinds - font, background colour and pattern, borderlines, etc.) with something like this:


    Code
    1. Cells(1, 3).Format = Cells(1, 1).Format


    This does not work. How can I do it, please? I'd rather avoid using copy and paste format, as it slows down the programme.


    Thanks for help!


    Jiri

  • Re: "Copying" cell format in VBA


    hi Jiri,


    why not copy the cell directly?
    try:

    Code
    1. Cells(1, 3).Copy Cells(1, 1)

    Cheers
    ___________
    Xlite :sheep:
    [st]All you need to learn VBA is an internet connection and Ozgrid.com[/st]

  • Re: "Copying" cell format in VBA


    I agree with xlite, the shortcut would be to just copy the cell (don't worry about a formula coming over) and then set the cell equal to the value you want.


    Code
    1. Cells(1, 3).Copy Cells(1, 1)
    2. Cells(1, 1).Value = Cells(1, 3).Value


    However, I kinda suspect you were just providing us with an oversimplified example to ask the question. If you just want to copy formats from one range to another it looks like this...

    Code
    1. Cells(1, 3).Copy
    2. Cells(1, 1).PasteSpecial (xlPasteFormats)
    3. Application.CutCopyMode = False

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: "Copying" cell format in VBA


    I was just going to ask a question along similar lines, so please permit me to jump in here ...


    I am replicating data across Rows for each Employee & have used Cut & Paste, but I have lost all my Column widths so therefore have "#####" all over the place.


    Now I immediately noticed that novakj says C&P slows the program down, but then looking at xlite's then Aaron Blood's replies it looks like you are copying format information but not actual data.


    Can I use this method to do both ?


    Basically, I suppose I am asking how to replace the following code, which copies data but not width information :


    Code
    1. For EmployLoop = 1 To LastEmp
    2. Range("E1", "I" & LastEnh).Copy
    3. ActiveCell.PasteSpecial
    4. ActiveCell.Offset(0, 5).Activate
    5. Range("K1", "O" & LastEnh).Copy
    6. ActiveCell.PasteSpecial
    7. ActiveCell.Offset(0, 5).Activate
    8. Next EmployLoop


    Any help much appreciated ...

  • Re: "Copying" cell format in VBA


    Well I can start a new thread if you really want, but my question is exactly the same as novakj's & I didn't want loads of "Cross Post" messages !!!


    When I copy data from one range (E1:I99) to a new range (P:1), the data is copied but the formatting, in particular the Width, is lost ...

  • Re: "Copying" cell format in VBA


    Quote from vodkasoda

    Well I can start a new thread if you really want, but my question is exactly the same as novakj's & I didn't want loads of "Cross Post" messages !!!


    No it's not. :)


    And starting a new thread isn't considered cross posting.


    Cross posting is when you post a question in another forum.

  • Re: "Copying" cell format in VBA


    Quote from norie

    No it's not. :)


    And starting a new thread isn't considered cross posting.


    Cross posting is when you post a question in another forum.


    Then please accept my apologies, I will start a new thread ...