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

    Adapt Code (2003)

    I am trying to adapt the code in the attached spreadsheet to my data. I have modified my data to look like the data in the spreadsheet (number of columns, number of rows etc) just to test. I keep getting a runtime error 1004 - method 'name of object'_ worksheet' failed
    It highlights - wsnew.name = c.value
    It separates the data according to rep but then it adds an extra tab with just the next number sheet tab. Say - Sheet7 after it puts all the rep names.
    It leaves the extra columns it creates because the code never gets to - ws1.columns("J:L").delete.

    What am I doing wrong that I keep getting this error? Thanks for any help you can provide.

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

    Re: Adapt Code (2003)

    Do you perhaps have a blank-looking cell in the Rep column that is not really blank? Try single-stepping through the code (click somewhere in your macro and press F8 to single-step). When you have executed the line
    <code>
    r = Cells(Rows.Count, "J").End(xlUp).Row
    </code>
    hover the mouse pointer over r to see its value. It should be the row number of the last populated cell in column J. If the cell in row r (in column J) looks blank, that may cause the problem.

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

    Re: Adapt Code (2003)

    I walked through the code. I walked through each rep. After the last rep - c = empty instead of a rep name. Then I get the error. I think it is c and not r that is the problem. I don't know why though. It looks like the code in the original sheet.

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

    Re: Adapt Code (2003)

    I checked my data in the Rep column and there was a blank cell. You were right. Once I fixed that the code worked. Thanks for your help.

Posting Permissions

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