Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Copying Data but not Conditional Formatting (Excel 2000)

    Hi,

    I have the following code:

    <pre> Range("Matches").AdvancedFilter Action:=xlFilterCopy, _
    CriteriaRange:=Range("AdCri2"), _
    CopyToRange:=Range("AdData"), Unique:=False
    </pre>


    It copies data and normal formatting fine, but seems to ignore conditional formatting completely.

    It seems to even allow conditional formatting to remain when it pastes data over cells with conditional formatting.

    This is not what happens when copy and paste are used from the keyboard.

    Any ideas on what is happening?

    Thanks in advance.

    Peter Moran

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying Data but not Conditional Formatting (Excel 2000)

    I think this is by design. Conditional formatting can be quite complex - it can look at the cell values, but it can also use formulas that look at other cells, with absolute or relative references or a mixture of those. It would be virtually impossible to decide how these conditional formatting rules should be copied - how to handle the absolute and relative references, for example.
    So you'll have to apply conditional formatting to the target range yourself.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copying Data but not Conditional Formatting (Excel 2000)

    Thanks Hans,

    I guess I should never have expected that Copy and Paste in VBA would be identical to what happens using the keyboard and menus!!

    Such is life, such is computing!

    Peter Moran

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying Data but not Conditional Formatting (Excel 2000)

    The cause is a bit different.

    If you use Advanced Filter with the "Copy to" option interactively, you'll see exactly the same behavior as in VBA. The "Copy to" option of Advanced Filter does not use the standard copy/paste operations.

    If you use the Copy and Paste methods of a Range object in VBA, you'll find that conditional formatting will be copied.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Copying Data but not Conditional Formatting (Excel 2000)

    Thanks again Hans.

    I can now see the distinction.

    Regards,

    Peter Moran

Posting Permissions

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