Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro not working (2003)

    I am getting an error message running the attached macro. I included the error I am getting also. The column that has the names that are used for the macro, have no blanks in that column and none of those special characters. It highlights the following part of the macro: Wsnew.name = c.value. I can't figure out what it is trying to tell me.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro not working (2003)

    What is the name you are trying to enter (what is "c.value"?) at this point in the code?

    Steve

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

    Re: Macro not working (2003)

    It means that one of the values in column Z is not suitable as a worksheet name. The error message tells you what isn't allowed. Check column Z carefully.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2003)

    This spreadsheet was saved from a program called Cognos8. When it saves a report as Excel, it does something to the Excel worksheet. I retyped the field that had the rep names in it and then I didn't get the error but all of the other fields that were supposed to be in each tab was blank. So the rest of the spreadsheet must have weird formatting or something. Is there anyway that isn't like retyping the whole spreadsheet where I can change it back to normal excel worksheet.

  5. #5
    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

    Re: Macro not working (2003)

    Don't forget to check the name length and for any duplicate names.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Macro not working (2003)

    The macro already checks for duplicate names, but not for "illegal" characters and excessive length.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2003)

    Is there some way to remove all formatting or whatever is in a spreadsheet and get it back to basics? I don't know if this is possible but I thought I would ask. Thanks for any help you can supply.

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

    Re: Macro not working (2003)

    Could you attach a small sample workbook (zipped if necessary)?

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2003)

    Here it is.
    Attached Files Attached Files

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

    Re: Macro not working (2003)

    Are you sure that this worksheet is representative? Z1 contains "Rep" but O1 contains "Client#". Because they don't match, the advanced filter action fails.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2003)

    I just didn't adjust the macro for the sheet I sent you. I have changed it so many times in testing that from the time I sent the macro and now, I moved things around.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2003)

    I just looked at what I sent. I must have tried to run the macro and it aborted and left what is in column Z. Just delete those columns. Sorry, I forgot to look past the regular data.

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

    Re: Macro not working (2003)

    OK, when I do that, the macro runs without error. So the macro itself is OK. Apparently, your real worksheet contains data in column O that violates the rules for worksheet names. Could you answer Steve's question higher up in this thread?

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2003)

    How did you get the macro to work? I ran the macro on the same example I sent you and I get an error like I sent earlier. There is something wrong with the column that the rep names are in - not the name themselves.

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

    Re: Macro not working (2003)

    The macro in the Word document, when run on the workbook that you attached, acts on the Client# column, not on the Rep column.

    When I change the code to make it act on the Rep column, it chokes because the rep names have lots of trailing non-breaking spaces after the actual name. They are all 35 to 38 characters long. To correct this, you can change the line
    <code>
    wsNew.Name = c.Value
    </code>
    to
    <code>
    wsNew.Name = Replace(c.Value, Chr(160), "")
    </code>
    This removes the non-breaking spaces from the names.

Page 1 of 2 12 LastLast

Posting Permissions

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