+ Reply to Thread
Results 1 to 5 of 5

'Select method' failure

  1. #1
    Registered User
    Join Date
    09-30-2008
    Location
    ca
    MS-Off Ver
    excel 2007
    Posts
    19

    'Select method' failure

    My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure

    'error 1004 Select method of range class failed'

    when trying to select the column to copy,

    Please Login or Register  to view this content.
    The sub is
    Please Login or Register  to view this content.
    This is run from a command button on a summary sheet. Could anyone tell me why this fails on selection of the column to copy? Thanks
    Last edited by VBA Noob; 10-30-2008 at 02:03 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-11-2006
    Posts
    407
    I think the error comes from "unprotect" removing your "copy" command. If you unprotect the sheets before you copy that should solve the issue. Also, to speed your code up, you can avoid using "select". Try this code instead:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-30-2008
    Location
    ca
    MS-Off Ver
    excel 2007
    Posts
    19

    select method failure

    Works perfectly, thank you muchly.
    If I may presume, to copy more than one column, discontinuous, eg. column H and P, do I need to repeat the process for each selection, or can I somehow say ' With .columns "H" and "P" '?
    The column "H" has data validation based on a list on the worksheet(column "P"). Is there a way to copy the validation formula as well when copying the column? Pasting formulas doesn't seem to do it.
    Last edited by kysaul; 10-28-2008 at 05:56 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    04-11-2006
    Posts
    407
    Something like this?
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-30-2008
    Location
    ca
    MS-Off Ver
    excel 2007
    Posts
    19

    select method failure

    Very neat and works a treat! I thank you.
    I solved the copying data validation problem, using a named range on the master sheet as the source instead of a range on each sheet. Not sure why that works better. Thanks again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1