+ Reply to Thread
Page 2 of 10 FirstFirst 1 2 3 4 ... LastLast
Results 16 to 30 of 139

Add row at end of groups to add certain columns

  1. #16
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,647

    Re: Add row at end of groups to add certain columns

    Hi Ginger
    Leave it open until you're satisfied all is well.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  2. #17
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    John: Well, I'm back to the same obstacle. I tried to sort what I have so far in May, and it did not work. I got the error message I mentioned above. Sending you my latest file to see if you can tell me what I am doing wrong. Thanks, Ginger.
    Attached Files Attached Files

  3. #18
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,647

    Re: Add row at end of groups to add certain columns

    Hi Ginger

    Silly mistake on my part...this code in Sort Stuff
    Please Login or Register  to view this content.
    should be this
    Please Login or Register  to view this content.
    Do you see the difference? Notice the . (dot) in front of Cells (.Cells) in two places.

    Try this version of Macro Book...let me know of issues.
    Attached Files Attached Files

  4. #19
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    Yes. I see the difference. I tried it and it worked. I will leave open until the end of May when I will do the final sort, and then I will close. Fascinating how it works, but way beyond my understanding. Thank goodness for the "gurus." Ginger.

  5. #20
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    Tested the macro book and file several times. It worked perfectly to sort and add all my consignments. Now, I am getting a bit greedy. Since I have the consignments sorted and added, would be possible to do the whole worksheet so I have my dealers sorted and added. Right now, I sort out the worksheet for the dealers, add each dealer's total, and then transfer info to another workbook to make adjustments (credits, rent, etc.) and come up with what I have to pay them. Dealers do not have a "c" in the consign column. It could go in the D & C sorted tab. What do you think. I assume I would use the macro book you gave me for consignments and another macro book for dealer sort? If this is a big deal, never mind, you have made my life so much easier already. Thanks, Ginger

  6. #21
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,647

    Re: Add row at end of groups to add certain columns

    Hi Ginger

    Nah...I don't think this would be a big deal...and you wouldn't need an extra Macro Book. The code to do Consignments and/or Dealers could/would be in the same book and could/would be run from the same Button or could/would be run from separate Buttons as you wish. I'd need to see the layout of D & C sorted tab. Post a workbook where you've done your ENTIRE process for ALL worksheets and explain the process...perhaps we can make your life simpler.

  7. #22
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    Basically, I have to sort out the consignments and add each sale up for each name (which you have already done for me with the macro book). the consignors pay me 30% of their sales and any credit card fees, and this is already reflected in the worksheet, so it is all taken care of when I use your sort. I do not write checks for them unless they come into the store and request their check.
    Next, I have to sort out all the dealers (the ones that do not have a "c" in the "consign" colum, and add totals for them. They pay me 10% plus credit card fees and rent. Once the sort is done for dealers, I need to make adjustments for their rent, any credits, time worked in the store as a credit, etc., and place in their individual worksheets as a separate file so I can give them a copy. This is fairly straightforward and does change from month-to-month. I have attached a file I prepared for one of my dealers for April. Let me know if I should send anything else. thanks, Ginger
    Attached Files Attached Files

  8. #23
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    Oh, I just read you last note again. I do not have a specific layout for the D & C tab. I used to copy over the dealer sort and put it there, but I haven't used the D&C table in a while, but did not remove it. I would just copy my worksheet into another workbook so I could manipulate without harming my original.

  9. #24
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,647

    Re: Add row at end of groups to add certain columns

    Hi Ginger
    I'm not certain what you're looking for. We'll develop something similar to what we have for Consignments. After that you'll need to tell/show me what you'd really like it to be.
    Get back to you in a bit.

  10. #25
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,647

    Re: Add row at end of groups to add certain columns

    Hi Ginger
    The code in the attached creates worksheet D & C Sorted for all non-consignment records in ActiveWorkbook.Sheets(1)...which in the case of the sample file you posted is named "May 2012". This worksheet (D & C Sorted) has the same layout as the "Consignments Sold This Month" worksheet we previously developed. The only difference is that I've added a total to Column H as you appeared to be using this in your sample file "hubbart from April 2012".

    I'm fairly confident we can take this up at least one more level. For example, in your "hubbart from April 2012" file, I notice you combined "Hubbartt-Pat DPH" and "Hubbartt-Pat DPHD" and "Hubbartt-Pat DPHV" into the same Report/File. I'll look at this...in the meantime let me know if the code in the attached does anything meaningful for you.
    Attached Files Attached Files

  11. #26
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    I used the new macrobook. Everything worked perfectly. the "Hubbartt-Pat DPH" and "Hubbartt-Pat DPHD" and "Hubbartt-Pat DPHV" all sorted individually, so that is taken care of as well. The only thing I have left to do is to transfer the non-consignment names into the individual cumulative file, which is easy for me to do. I don't believe you can automate it further for me because the rent and credits vary from month to month. I have included one sample file for Tower so you can see what I do with the individual non-consignment names. I think what you've done will work beautifully. I will do my final sort and end-of-the-month settlements on June 4. If all works well, I can mark this solved. What a difference!! Saving me many hours. Don't want to sound like a broken record, but I really appreciate your expertise. Ginger
    Attached Files Attached Files

  12. #27
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,647

    Re: Add row at end of groups to add certain columns

    Hi Ginger
    You're welcome...glad to be of help.

  13. #28
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    I just finished my month of May and it is attached in the "SORTED" file I have attached. I do not need the Column H totaled. It is irrelevant. You will notice in the D & C tab that I have added the rent and total of the check I am to write the person, which is the "total due" minus the "rent" for that individual. I had to cut and paste each two lines from another list for these dealers and doublecheck the formula. I am wondering if a "rent" row could be added and then a row with the formula already in it for "total due" minus "rent" for the grand total (or whatever it should be called). Then I would only have to make about 30 entries monthly under the "rent" column to get the whole thing done. I found the cutting and pasting time consuming. I realize we cannot add a rent figure ahead of time since it is different for each person, but a simple blank would be nice after the names are sorted and "total due". Also in the sheet, there is a tab marked "box". I put my Hubbartt figures in there. I think it used to be labeled Hubbartt, but I must have inadvertently changed it. I also notice a tab "my lists." I think that is something you added. I noticed there were several mistakes in the list that had to do with incorrect entries of mine in the first place, but i did not change anything because I didn't want to screw it up if it served a purpose in the sorting macrobook. For instance, the Sister Stones-16 is the only name for that dealer. I had mistakenly filled a series so it wound up with Sister Stones-17, 18, etc. Let me know if the above edit is possible.

    Also, on a separate note, I note that the only work I can do on the monthly spreadsheet is at home on my 2003 software. When I put it in my "dropbox" so I can see it at work (2007 software) if I need to, none of the keyboard macros I use and the macrobook do not work. I know this is unrelated to what you've been working on, so should I ask a new question in a new post?
    Attached Files Attached Files

  14. #29
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2007 2003
    Posts
    12,647

    Re: Add row at end of groups to add certain columns

    Hi Ginger
    The total for Column H has been eliminated, the RENT and TOTAL rows have been added and the Formula for summing the gross to net has been inserted and an additional blank line has been inserted.

    Regarding this
    I realize we cannot add a rent figure ahead of time
    Aha, not true ma chérie...if you were to include in the rent tab the NAME as it APPEARS in the May 2012 tab I believe we could pull the RENT into the D & C sorted tab. I'd be willing to try if you wish to pursue this.

    Regarding this
    I also notice a tab "my lists."
    That sheet (myLists) is recreated each time you click the Dealers Button. So, if you have incorrect entries in the May 2012 tab (such as you described for Sister Stones-17, 18, etc), simply change the incorrect entries in the May 2012 tab then rerun the procedure...they'll be corrected in D & C sorted on the rerun.

    Regarding this
    Also, on a separate note, I note that the only work I can do on the monthly spreadsheet is at home on my 2003 software. When I put it in my "dropbox" so I can see it at work (2007 software) if I need to, none of the keyboard macros I use and the macrobook do not work
    I've tested the procedures (and the short cut keys) in both Excel 2000 and Excel 2007 and they perform as expected in both versions. I've assigned short cut keys of CTRL + r to Consignments and CTRL + s to Dealers. Let me know if this continues to be an issue...I can't recreate it here.

    Let me know of issues and of how you wish to proceed on the RENT question.
    Attached Files Attached Files

  15. #30
    Registered User
    Join Date
    03-12-2012
    Location
    pismo beach, california
    MS-Off Ver
    Excel 2003
    Posts
    96

    Re: Add row at end of groups to add certain columns

    Yes, yes, yes. If you can do rents perfectomundo. If the rent amount should change in the future, would I just make sure it is changed on the RENT tab in my worksheet?

    OK. I understand what you said about "my lists." Will I leave the list as is when i do my june entries? Or do I clear out the list before proceeding?

    I will try the shortcut keys at work and see what happens. I also had a hard time working on my worksheet at work (2007) because none of the keyboard shortcuts (macros) I use for moving around in the colums quickly did not move with the worksheet. I suspect there was something I had to do to move the macros list over with the worksheet, but frankly, I gave up, and I just don't edit my worksheet at work. That works OK for me, but someday I would like to understand the method for moving over the macros. Just to inform you, when I open my worksheet at home, it also opens two other files with it, and I always assumed these were needed for what I was doing. They are "personal.xls" and personal (version 1).xls.

    When I do a new worksheet (say, for June), I use the old sheet for May, and delete values (but not formulas). So I assume I will continue to do that. I tried to create a template to pull up each time, but, alas, it got completely messed up. Yikes.

+ Reply to Thread
Page 2 of 10 FirstFirst 1 2 3 4 ... LastLast

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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