Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a macro that uses AutoFilter to filter a list on one sheet. I then use
    Range("A1").CurrentRegion.Copy
    Sheets("Calculation").Select
    Range("A4").Select
    ActiveSheet.Paste
    to paste the filtered data to a second sheet.
    However, I receive an error message saying that the Method Paste failed even though
    the data does seem to be copied correctly.
    I've tried using Activate rather than Select for the Calculation sheet, but still receive
    the error message.
    What am I doing wrong? Thanks, Andy.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is it any better if you use:
    Code:
    Range("A1").CurrentRegion.Copy Sheets("Calculation").Range("A4")
    instead?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='792087' date='04-Sep-2009 10:50']Is it any better if you use:
    Code:
    Range("A1").CurrentRegion.Copy Sheets("Calculation").Range("A4")
    instead?[/quote]
    Immediate response! But no, I tried that already. Andy.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Which version of Excel and do you have any hidden columns, or around 8000 discontiguous rows due to the filter?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='792089' date='04-Sep-2009 10:59']Which version of Excel and do you have any hidden columns, or around 8000 discontiguous rows due to the filter?[/quote]
    Excel 2003. There are no hidden columns and only a few rows currently.
    The filtered data was originally from an external source, but this shouldn't make any
    difference should it? Andy.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No it shouldn't. Can you post the workbook (removing any confidential info)?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='792091' date='04-Sep-2009 11:17']No it shouldn't. Can you post the workbook (removing any confidential info)?[/quote]
    Attached.
    I've got it to work using Advanced Filter, but would prefer that the first version worked.
    Thanks, Andy.
    Attached Files Attached Files

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Your first version works fine for me in 2003! Which SPs do you have installed? (I'm only on SP2 here)
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='792094' date='04-Sep-2009 12:10']Your first version works fine for me in 2003! Which SPs do you have installed? (I'm only on SP2 here)[/quote]
    I'm using a version without Service Packs. Perhaps I should use the Advanced Filter
    version - they always seem more robust that using AutoFilter. Andy.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In my experience, Autofilters work much better in code if you specify the actual range they should work with rather than using just the first row or first cell to set it up for example.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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