+ Reply to Thread
Results 1 to 139 of 139

Add row at end of groups to add certain columns

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

    Add row at end of groups to add certain columns

    I need to create a macro that I can insert at the end of a group of figures on a worksheet that will add columns "unit," "total," "vam%", cc3%, and "check". I then need to be able to move down or up to another group and do the same thing. What I created just returned to the same row. Please see example I have attached
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need macro to add row at end of groups to add certain columns

    An good sample workbook would show us BEFORE and AFTER examples, probably on separate sheets in the workbook.

    I don't see any code in that workbook so I can't comment on what you've tried so far.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

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

    Re: Need macro to add row at end of groups to add certain columns

    Hi vtaylor
    This code is in the attached and appears to do as you require. Let me know of issues.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    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.

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

    Re: Need macro to add row at end of groups to add certain columns

    John: I pulled up the attachment and pressed the "RUN ME" box you provided, and it worked perfectly. Since my worksheet will change each month, how do I incorporate the macro "run me" to each monthly spread sheet? I first have to break out each group from my major sort so they are in a position where I can add the total line. I am a novice, so macros I have in my spreadsheets are assigned to keystrokes and that's how I use them. This is a new concept to me. Thanks. What you have done is wonderful. Ginger Taylor

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Need macro to add row at end of groups to add certain columns

    Hi Ginger

    You can assign the Macro to key strokes and delete the Run Me button. If you like I can do that and re-post the file. If so, what key strokes would you like?

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

    Hello John: Yes, I would love it if you would assign a key stroke. Then, my question is how do I get it into my recurring worksheets that change monthly? I use the same template over and over that already has some complex formulas that I got help with before, and I just clear out the names and figures to protect the innocent, and then input new data. I read the help file how to copy a macro from one workbook to another. Got a little confused using a sample file, but will try it slowly once again after the macro is working. Also, on the sample I gave you, each group is separated by 3 blank rows before I add them up. I assume I need to insert exactly three rows before I run the new macro and add up the groups? There are usually about 50 groups, so this is going to be a real timesaver for me. Really appreciate it. I live on the central coast of California. Too bad you are not closer, I would treat you to a dinner in thanks. Ctrl R or Ctrl T would be good keystrokes for me. Ginger

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Regarding this
    each group is separated by 3 blank rows before I add them up
    The file I'm working with was not presented this way but that's beside the point. The code has been modified to insert a new row between each group.

    Regarding this
    my question is how do I get it into my recurring worksheets that change monthly?
    You don't really need to...there's a much simpler way. I've moved the code to a separate workbook called "Macro Book.xls". All code has been removed from the "Data" file (in this case, myTemplate).

    Macro Book code will prompt you for a File Name to open and then run the Add_Stuff procedure on that newly opened file. This can be further simplified if you'll let me know the name of the Template File and where it lives (what file path). The code can be modified to automatically open the Template File.

    For purposes of testing, place the two attached files in the same Folder (matters not where). In real life, the files can be in different Folders and the code will be modified slightly. Open the Macro Book. The code can be activated by clicking on the "Add Totals" Button or with CTRL r.

    Let me know if all this works for you...or not.
    Attached Files Attached Files

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

    Here is a new sample. I use the individual tickets I entered into one worksheet and then sort out consignments (which appear in Sheet 1). Then I need to separate each name in Sheet 1 into a group or individual so hat I can add headings and total columns at the bottom and write a check to that person. I envisioned that I could create a macro that added the headings and assign it to a keystroke, and I envisioned a macro to add the colums. The first macro you sent me to ADD STUFF worked perfectly, but when I tried to do it to many more names than the sample I sent you, something happened and it did not work. The macro I created to add HEADINGS just put the headings in the wrong place and not in the row I needed and where I was when I ran the macro to add the HEADINGS. I tried to send the whole workbook, but it would not upload, so I narrowed down to Sheet 1. Not sure if this form allows me to ask so many questions, but I am hoping it's ok. Thanks, Ginger
    Attached Files Attached Files

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

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

    Hi Ginger

    It would be helpful to see the whole file so I can get a better feel for the entire effort. How large is the file? Try zipping the file and attach the .zip file. Also, please post whatever code you have, eg
    The macro I created to add HEADINGS just put the headings in the wrong place and not in the row I needed
    Please be sure to use Code Tags when you post the code...better yet, include it in the workbook that you upload.

    In the meantime, I'll look at the recent uploaded file.

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

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

    Hi Ginger
    Try the code in the attached. The code creates your headers and totals in your sample file. If I can see a better sample of your actual file we can also extract the consignments all with the push of a button (or CTRL->r).

    Let me know of issues.
    Attached Files Attached Files

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

    OK. Trying to send whole file. I don't know how to zip, so I tried to learn. Hope it worked. Everything is based on the one worksheet. Those rows marked with a "c" in consignment are the ones that need to be sorted, headings added, and totaled for each name. For the rows that do not have a "c" in consignment, I sort out the names (dealers) and have no problem doing what I need to do with them (I have to add each total, deduct rent, and come up with a final total to write them a check). I put all the dealer information into separate workbooks for each dealer and then do the calculations on them. It is the "c" (consignments) that take up so much time. I copy the whole worksheet and paste it into the backup copy tab so I can manipulate and not worry about screwing up the original. When you talk about codes, it begins to go over my head. For instance, on your last note, I don't know what to do with the macro book file to get it into my monthly workbook. I already sorted out consignments in the file I am sending. At the end of the month after I do what I do, I save the workbook as the next month (May), and I delete the numbers, but keep all the macros, formulas, etc. that were present in the last month. For instance, if there is a "c" in consignment, the income to us is 30%. If there is no "c" then it automatically calculates 10%, which is what the dealers pay us. If there is an "x" in resale, then it does not calculate tax. If there is a "c" in CC, then it calculates a credit card fee that the dealer or consignor owes us. The end column is CHECK, and that is what we pay either the dealer or consignor. Yikes, this is hard...... There are other worksheets contained that have nothing to do with payment, but are updated monthly to keep current (rent, codes and autocorrect, consignments received, etc.)
    Attached Files Attached Files

  12. #12
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    The file came across just fine. I've revised the code based on the structure of this file. It matters not what this file is called. The code works with three of the worksheets contained in the file:
    1. The April 2012 worksheet...this is your Raw Data worksheet and it MATTERS NOT what this worksheet is called. However, it MUST be the FIRST worksheet TAB (the first to the left as it is in the Workbook you posted).
    2. The Backup Copy worksheet. This worksheet MUST be named Backup Copy but can be anywhere in the TAB lineup (doesn't have to be the second TAB...can be anywhere).
    3. The Consign sold this MO. This worksheet MUST be named Consign sold this MO but can be anywhere in the TAB lineup (doesn't have to be the fifth TAB...can be anywhere).
    Now, you have this concern
    When you talk about codes, it begins to go over my head. For instance, on your last note, I don't know what to do with the macro book file to get it into my monthly workbook
    The Macro Book DOES NOT go into your monthly workbook. The code in Macro Book only works with the data in your monthly workbook.

    Place the attached Macro Book and a COPY (emphasis on COPY!!!) of your monthly workbook in the same folder...don't care where. Open the Macro Book in Excel. Click the Button (or CTRL->r). You'll be presented with this screen open file.jpg

    Single Left Click on your monthly file name then Single Left Click on Open...and, voilà, your totals are in Consign sold this MO worksheet.

    Let me know of issues.
    Attached Files Attached Files

  13. #13
    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: Thanks. I am gone for two weeks, but when I get back, I will follow your instructions carefully and let you know. Ginger

  14. #14
    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: I took time before leaving to test what you have given me. I placed the MACRO BOOK and a COPY of my monthly workbook in the same folder. I opened the MACRO BOOK in Excel. I clicked the Button. I single left clicked on my workbook name. Then I single clicked on OPEN. I got a "run-time error '1004', application-defined or object-defined error. I pressed END. The result was the consignments were listed in the "consign sold this MO," but they were not sorted by name, separated by rows, and totaled.

  15. #15
    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: I tried one more thing. I used my current Store Monthly Sales for May COPY to test your work. BUT, I renamed it before using it by putting your name first so it looked like this "JOHN Store Monthly Sales May 2012." When I renamed it "Store Monthly Sales May 2012 COPY" and tested it, it worked fine!!! So, I am excited to test it at the end of the month for May. So much time I was spending to do this. I am so grateful. Shall I leave this post open until the end of May? Or do I close it now? Ginger

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

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

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

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

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

    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

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

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

  21. #21
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    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.

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

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

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

    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.

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

    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

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

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

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

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

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

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

    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

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

  31. #31
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Regarding this
    If the rent amount should change in the future, would I just make sure it is changed on the RENT tab in my worksheet?
    Yes, BEFORE you run the Macro...however, lest you forget, simply update the Rent tab and rerun the Macro.

    Regarding this
    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?
    Forget it's even there...the Macro does what it needs to do to maintain it.

    Regarding the shortcut keys and Macros...they'll follow the Book...if they're IN the Book. This may well be related to this
    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.
    Did you write the original code? Your "other" macros (and the shortcuts to them) may well be stored in these "personal.xls" workbooks. Since these "personal.xls" workbooks don't exist on your work computer they won't run...just thinking out loud here.

    I personally don't use "personal.xls" files for this very reason...they DON"T follow the Book. If you wish to "fix" this send me copies of the "personal.xls" files. You'll first need to find where they're stored and that depends on what version of Excel you're running. Do a search on your hard drive for "personal.xls"...you'll find them. Send the files and we'll incorporate the code into "Macro Book".

    Regarding this
    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
    If I understood more about your process we could automate this...if you and I were sitting in front of the same computer it would be rather simple (I think). There are free tools available out there that "virtually" allow this. I'm not suggesting we do so...just food for thought.

    In the meantime, if what you've done in the past has worked, try it, if it doesn't work, let me know...we'll figure something out. Keep in mind, you don't NEED to wait until month end to run the procedures we've been working on...you can rerun them on your data files as often as you wish...your original data file is NOT modified in any way.

    When you're done with May post the file. In the Rent tab include in Column D
    the NAME as it APPEARS in the May 2012 tab
    Having done so, I'll demonstrate how we pull the Rent into D & C sorted.

  32. #32
    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 am attaching a file called "rent list". This replaces all the information in the RENT tab on the May worksheet. One of the names will be off the list in July. How would I handle changes deletions or additions and still have it work in the dealer total sales minus the rent sort?
    Attached Files Attached Files

  33. #33
    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 wrote the code with some help and they work well, helping me move around my worksheet.
    Attaching a new zip for May that incorporates the proper names for the RENT tab. I sent the above "rent list" before I read you latest note. Well forget that. I just tried to send the May file with the RENT tab corrected, and it kept failing. Can you use the last one I sent? I am attaching the personal.xls and personal (version 1.xls). I am happy to give you control for side by side. How would I do that?
    Attached Files Attached Files

  34. #34
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginny

    The attached workbook contains code that incorporates application of rent and also the Macros that were in your Personal.xls file. The shortcut keys are the same as before.

    With regard to the rent worksheet, you don't need to delete names with no rent for the current month...just leave it blank. Let me know of issues.
    Attached Files Attached Files

  35. #35
    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. OK. Will check out new macro book tonight or tomorrow. Question: As I work daily on my monthly sales entries (before I ever need to sort, etc. using macro book v 3.1.xls), how do I get rid of the two personal.xls files that always open with it and use your method of having my macros attached (via macrobook?) so they move with the spreadsheet to the dropbox and back again to home so I can use my monthly sales workbook at my store and at home? The personal.xls files do not transfer with the workbook when I open it at the store. Also, are the custom autocorrect entries I have made contained in the personal.xls files, or is that something separate? I notice if I create an autocorrect for my workbook, I must create it at home and then again at work. This is only a minor deal--just wondering? Ciao for now. G

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

    You said I can run the macro book to sort at any time, don't have to wait to the end of the month. If I do, and then a couple of days later want to run it again, does it replace the previous sort with the new, or do I have to delete entries in the consignment and dealer tabs that previously sorted so it starts with a blank tab? Ciao the second time. G

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

    Hi John. I did some entries for my June monthly sales. I ran the Macro Book v 3.1 but it did not work on the D & C tab. It pulled the rent in OK, but it did not total the entry, deduct the rent, and then give me a final total. I ran it on the old May workbook and it seemed to work fine. I am attaching the June sheet with the entries I have so far as well as your macro book latest v3.1. What am I doing wrong? Thanks, Ginger
    Attached Files Attached Files

  38. #38
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginny
    Regarding this
    how do I get rid of the two personal.xls files
    These files live in your XLSTART folder. You'll need to do a search for that folder...having found it you can DELETE the Personal(Version1) file...there are no Macros in this book. You can then RENAME Personal.xls to something (perhaps Personal.Savexls). These books should then not open when you open Excel.

    Regarding this
    do I have to delete entries in the consignment and dealer tabs that previously sorted so it starts with a blank tab
    No, the code clears these two tabs (and also the Backup tab) each time these procedures are run.

    Regarding this
    I did some entries for my June monthly sales. I ran the Macro Book v 3.1 but it did not work on the D & C tab.
    There's a couple issues that caused this. One is that the Headers were erased from the Backup tab worksheet. I've modified the code such that if it were to happen again the code corrects it. The second issue is that Dates are missing in Column A of the June 2012 tab. These dates must be there or the code doesn't like it.

    Try the attached...let me know of issues.
    Attached Files Attached Files

  39. #39
    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: Sorry about the date thing. I did not notice. Tried again, but see the result in the attached v4.1 under the D&C tab. Can't figure out why these entries marked "question" did not work. I reentered the original item and they calculated correctly but did not make it to the D&C correctly. All the other entries are correct as far as I can tell, but did not do an item-by-item proof yet.
    Attached Files Attached Files

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

    Hi John: I deleted the personal (version 1).xls. I did not do anything to the personal.xls yet because I wanted to understand: The keyboard macros that I use when entering data are still in my file when I open it? Or do I have to open your Macro Book first? Cheers, Ginger

  41. #41
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    In worksheet June 2012, unhide Column M. For the records on Line 39 and 40, Column M has bad data and will need to be corrected.

  42. #42
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    This code is in your Data File
    Please Login or Register  to view this content.
    This code, transferred from Personal.xls, is in Macro Book and the Macro Book must be open to run it. If you wish to run the code from the Data file, move this code to Data file and delete it from Macro Book.
    Please Login or Register  to view this content.

  43. #43
    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 don't need column M. Can I just delete it without affecting anything.

    #By "data file" do you mean the actual workbook I open? I notice on the "select code" example, that there is no Ctrl J or Ctrl U. These are macros I use as well. Are they someplace else?

    #I don't know how to move the code to the "data file" so i will have to figure that out. Is it as simple as copying and pasting into the visual basic list of macros that come up.

    #So, the end result (if I figure out how to move the code) will be that I open my workbook and my macros will automatically be in there, and no other files will open at the same time (for instance, the personal.xls because I will have renamed it and saved). THEN, when I want to sort, I will open the "Macro Book" and follow that procedure?

  44. #44
    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 fixed column M, but I don't need both columns. I ran the sort. Worked fine. Yippee!!
    #However, I did notice that there were two groups for VAM (this is the code for sales I own). sometimes I have to use an autocorrect that changes CF to VAM when I type CF. Otherwise, I just type VAM. Could this be the reason there are two groups? Ginger

  45. #45
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    Regarding this
    I don't need column M. Can I just delete it without affecting anything
    In a word...No...it will affect things. The code will need to be modified.
    Regarding this
    there is no Ctrl J or Ctrl U. These are macros I use as well. Are they someplace else?
    I see no code assigned to Ctrl J in any of the files you've sent me. I do see code assigned to Ctrl u but not Ctrl U. I have no idea where they're located. What do they do?
    Regarding this
    I don't know how to move the code to the "data file" so i will have to figure that out. Is it as simple as copying and pasting into the visual basic list of macros that come up.
    Yes, it's that simple. Open VBA...either insert a new module or open an existing Module and copy/paste the code. You'll probably need to assign the Short Cut keys.
    Regarding this
    the end result (if I figure out how to move the code) will be that I open my workbook and my macros will automatically be in there, and no other files will open at the same time
    That's the plan.
    Regarding this
    THEN, when I want to sort, I will open the "Macro Book" and follow that procedure
    That's correct...when your Data File is as you wish it to be, run the sort procedures in Macro Book.
    Regarding this
    there were two groups for VAM (this is the code for sales I own). sometimes I have to use an autocorrect that changes CF to VAM when I type CF. Otherwise, I just type VAM. Could this be the reason there are two groups?
    Possibly. There's "something" that's different among those records so they're being sorted separately.
    Let me know what I can do to help.

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

    Hi John: I wrote a long reply about what the macros do, etc., but forgot to post, so I am starting over.

    #Ctrl M and Ctrl N are the same and do the same calculation. I don't know where Ctrl M came from, but oh well, I will leave it there. So would I just "hide" it so it doesn't show up on the copy I give to the dealers/consignors?

    #I will check out to see if there are differences in the VAM entries.

    #I tried running my spreadsheet without personal.xls, but none of the macros worked. So, I copied all the code in every module in personal.xls to my worksheet and the shortcut keys actually work! Some were duplicate and I deleted those. For now, it looks like it's working. Whew. I am so inept. Am I ever going to be able to get rid of all the bugs. If I do, this will be the highlight of my year!! Thanks to you.

  47. #47
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    What do you give to the Vendors?
    the copy I give to the dealers/consignors?
    A hard copy or a copy of the file? You'll notice that Column M IS hidden in both the Dealer Sort and the Consignors Sort Reports. However, if you wish to be rid of Column M I can do so on the file copy I have and modify the code to accommodate that. It's not a complicated fix...you let me know.

    Glad you got your Macros back and intact.

    There ARE differences in VAM. It had me stymied so I tested it because you can't SEE any difference. Here's how I fixed it. I filtered June 2012 worksheet on Column C for VAM. I then drug the first VAM entry down to the last entry, cleared the Filter, saved and closed the file. Then I did the Macro Book procedure and came up with only one VAM report. Try it on a copy of your file...see if you get the same results.

    You're not "inept" if you're learning. "Inept" people choose NOT to learn. Let me know what you need.

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

    Hi John:
    I give the DEALERS a copy of the itemization of sales and any deductions and the actual check. I do this by cutting and pasting the appropriate sorted information into a single file for the dealer with his name. I do the same thing for SOME of the consignors, but not all. Some of them are single line items, and I just enter the info on the check I write to them when they show up for their checks (some don't come in for 2-3 months). The consignors who always get a copy are ABRAMS and GALLEGOS. If others have enough line items to deserve a copy, I just print a "selection" from the worksheet that has been sorted.

    I changed the personal.xls file to personalsave.xls, and when I open my spreadsheet, it opens the personalsave.xls along with it. To test whether I still had macros in my worksheet, I closed the open "personalsave.xls" file so that the only open file was my worksheet. The macros still worked. So, basically, I don't know how to get any "personal.xls" files to stop opening when I open my worksheet. I wonder if I just deleted "personal.xls" and "personalsave.xls" from the XLSTART folder whether that would stop a personal.xls file from loading, or it would just create another on on its own?

    Sure, we can do away with a repeat of the column entitled "CHECK" on the June 2012 worksheet. All I need is one column entitled "CHECK" that shows the individual sales minus any deduction for vam%, CC%.

    I assume the problem with the VAM entry is that sometimes it is a straight VAM that I type, and other times, I have receipts that are entitled CF (which is actually VAM), and I created an autocorrect to change CF to VAM, so in the future I will just enter VAM at the time of entering the data and do away with the problem. When you change the June 2012 with the fixes for Column M, will you send it back to me please and I will use that one going forward.

    I am attaching the v4 of the workbook that I am currently using.

    Guess we are almost there, yes? Only a couple of steps away, and we will have really streamlined my process. I am gaining some confidence....Thanks. G
    Attached Files Attached Files

  49. #49
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Delete personalsave.xls but don't delete personal.xls just yet. Rename personal.xls to personal.xlsSAVE and leave it where it is until we're sure all is good. It should NOT open with this new file extension. When you get done you should have ONLY Personal.xlsSAVE in the XLSTART Folder. Once you're comfortable you can delete it if you so choose.

    Apparently the Auto Correct feature adds some different formatting or hidden marker in the cell...don't know...should you continue to use it try the fix I suggested...it appears to work.

    I'll look at v4 tonight or tomorrow with a view toward deleting Column M in the June 2012 worksheet and modifying the code to accommodate that.

    CONFIDENCE...now that's what I'm talkin' about. I'll get back to you.

    PS: Be aware, any file in the XLSTART Folder with an .xls extension will open when you start Excel
    Last edited by jaslake; 06-13-2012 at 05:12 PM.

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

    OK. I see I renamed the personal.xls file incorrectly. What I did was move what was in the XLSTART to another folder, and that seemed to solve the problem. I will keep testing.

    I know what to do now with the VAM entries. So, another problemo down.

    Onward....Cheers. Ginger

  51. #51
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Attached are two files...Store Monthly Sales June 2012 v4.zip (with Column M removed) and Macro Book v4.xls (with the code modified for the removal of Column M).

    Let me know of issues.
    Attached Files Attached Files

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

    Well Hello, John. I think everything worked PERFECTLY!!! I tested on only a couple of days of sales, so I am going to enter more and test again before marking this SOLVED. How will I find you again if I have another question if I mark everything SOLVED? I can't believe this is actually happening! I used to have to take two days to get this all done. Now, in a few steps, it's done, and all I have to do is cut and paste the DEALERS individually into their own files. Yippee, Hooray, Happy Feet.....Hasta Mañana friend. Ginger

  53. #53
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger...glad all is working well. If you have further issues you can always PM me. Also, I'll leave the subscription live for a while so you can simply post to your Thread. Interesting project.

    If you truly have CONFIDENCE we can probably do this pragmatically
    all I have to do is cut and paste the DEALERS individually into their own files
    If you wish to pursue this let me know your current process...step by step.
    Last edited by jaslake; 06-15-2012 at 07:36 PM.

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

    Hi John: Sorry to take so long. Medical emergencies with my mom and family. I have been using the macro that you last sent and everything is working perfectly for two months now. I would like to have the dealers cut and pasted into their own individual worksheets so I can inclue their copy in the envelope with their check I write to them, but it seems too complex so I have just been doing it manually. One other thing that would be supper convenient would be to have the TOTAL DUE amounts for the worksheet listed at the end in a GRAND TOTAL. That would tell me exactly how much money I have to have in the bank to pay everybody. I have been doing it manually on a calculator. In other words, after sorting the dealers (D&C sorted), at the end would be a total of the TOTAL DUE amounts. Same for the consignments (consignments sold). BUT, I do not want to mess up my Macro 4 that I have been using. It has solved so many problems for me. What do you think? Cheers, Ginger

  55. #55
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    Glad all is working well for you and hope the Medical Emergencies resolved themselves positively.

    I think these items are do-able without messing up
    my Macro 4 that I have been using
    When you say "Macro 4" I assume you're referring to Macro Book v4.xls (as attached to Post #51)...is this correct? Want to make certain we're using the same procedures.

  56. #56
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    Try the code in the attached...I believe it addresses both issues you raised.
    Attached Files Attached Files

  57. #57
    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, correct, Macro book v4.xls. The new Macro book v5.xlsm works perfectly. Perfectomundo!! I see that the print preview lists exactly what I need and I can just press print. Voilà. Q: Is there a way to save each name separately as a file? It would result in approximately 39 individual worksheets for the dealers (for example). I could then move the separated-by-name sheets into each dealers folder on my computer (for historic reasons), which I have been doing manually. I fooled around on the print preview page, but none of my magic created individual worksheets I could file in the individual dealer's folder. It would work fine if the worksheets were just saved in the default folder I am working from where the monthly sales worksheet is filed, and simple for me to move to the individual dealer folders. Am I asking too much from one little macro book? Cheers. Ginger. P.S. Thanks for not closing me out when you didn't hear from me. What does PM mean? You said I could always "PM" you.

  58. #58
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I'm gonna make some stuff up based on what you've described...we'll see if it works for you. Get back to you.

    PS: By the way PM means Private Message...click on my name...you'll see Private Message...click that...see what happens.
    Last edited by jaslake; 08-20-2012 at 05:54 PM.

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

    OK. Will await your creation. Macro book v5 working super. Thanks, Ginger

  60. #60
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Try the code in the attached. The code to do this
    Is there a way to save each name separately as a file?
    assumes that, in your Store Monthly Sale File, the first worksheet is ALWAYS named in this convention "Month Year" for example "June 2012", "July 2012", etc.

    The code creates two new folders (if they don't already exist):
    1. Consignments
    2. Dealers

    within these two new folders it creates sub-folders for the month your running. For example, it'll look like this:

    Consignments
    June 2012
    July 2012
    August 2012
    etc...
    Dealers
    June 2012
    July 2012
    August 2012
    etc...

    and within these monthly sub-folders will be a separate workbook for each Consignor/Dealer. You can rerun the procedure as often as you require for any given month (it recreates the individual monthly Consignor/Dealer worksheets each time you click the button).

    Try it...let me know of issues.

    PS: I re-read your requirements and see that you wish to copy the new Worksheets to the INDIVIDUAL Consignor/Dealer Folder...d@#!...didn't pay attention to the DETAILS. I've ALWAYS had this issue which has ALWAYS resulted in MUCH rework. I'll look at it again.
    Attached Files Attached Files
    Last edited by jaslake; 08-22-2012 at 04:26 PM.

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

    OK. I will wait to hear from you again. Sorry. Is it OK to run macro book v8.xlsm in the meantime just to see how it works? I understand what you mean about the first worksheet ALWAYS named "Month Year." **Do you also mean there is a specific way to type this? For instance, should I spell out all the months (June, July, August) and add the year 2012 as you did in your example above. Sometimes I have abbreviated the months, but I am assuming I should spell them out, right? **P.S. How do you do that nifty way you select text and then insert it so it stands out the way you did above when quoting what I had said in an earlier reply?

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

    OK. I will wait to hear from you again. Sorry. I understand what you mean about the first worksheet ALWAYS named "Month Year." **Do you also mean there is a specific way to type this? For instance, should I spell out all the months (June, July, August) and add the year 2012 as you did in your example above. Sometimes I have abbreviated the months, but I am assuming I should spell them out, right? **P.S. How do you do that nifty way you select text and then insert it so it stands out the way you did above when quoting what I had said in an earlier reply? P.P.S. I tried v8. I could not open the individual dealer file and got this message, "Excel cannot open the file Hoving-DCH-AUG 2012.XLSX because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." Looks like all the dealer files are there but I cannot open them, and my Excel 2003 did not convert them. Ta Ta til we meet again. Ginger. Hope we can "suss" it out (new word I learned today).

    ---------- Post added at 01:30 AM ---------- Previous post was at 01:28 AM ----------

    Sorry, I sent one message before I meant to, so you got two that say almost the same thing. G

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

    Been on the internet trying to find out why I cannot convert the xlsx file to xls and finally gave up. the microsoft converter just didn't do the trick. Other files gets converted OK (like xlsm), but not xlsx.

    I did see one suggestion to just change the extension, so I tried that out. I could then open the file OK but the formula did not work at the end to deduct the rent from the sales to come up with the final total due. The formula cell references were different.

    I was also thinking that the way you have just put all the dealer files into one folder for the month would be OK. I just re-did my thinking and one monthly folder is OK. I don't want you to do all that extra work.

  64. #64
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    I'll change the code such that it saves as .xls files...sorry, didn't think of that.

    Regarding this
    I just re-did my thinking and one monthly folder is OK
    Let me know what it is you require...only the logic changes a bit to create separate folders for each Consignor/Dealer.

    Regarding this
    I understand what you mean about the first worksheet ALWAYS named "Month Year." **Do you also mean there is a specific way to type this?
    No, there is no specific way to type it. It just needs to be the FIRST worksheet in the file and needs to be descriptive of the Month and Year as it's used to name the individual file.

    Regarding this
    How do you do that nifty way you select text and then insert it so it stands out the way you did above when quoting
    Use the Quote Symbol Quote.jpg

    I'll develop the logic to create separate folders...it's almost complete.

  65. #65
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Sorry for the late response...had Grandfather duties much of the day. Had the code done this morning but failed to post it.

    I'm posting two Macro Books:
    • Macro Book v8.xls
    • Macro Book v9.xls
    Both Macro Books have been modified such that they save files in .xls so you should not have the .xlsx issue.

    Macro Book v8.xls does as it did before...it saves the Consignor/Dealer files to a monthly folder.

    Macro Book v9.xls saves the Consignor/Dealer files to Individual Consignor/Dealer folders.

    Pick your poison...let me know of issues.
    Attached Files Attached Files

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

    Started to try out the macros then looked at the clock. Got to get my grandson to high school. Will check tonight after work.

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

    Macro book v9 did exactly what I needed with one exception. Once the files were in their appropriate folders, the TOTAL at the bottom of the sheet was 0. In other words, the total sales were there, the rent was there, but the total had a 0. on one of the files I opened it flashed something about "circular" formula. Most of the files I tried, opened without the "circular" reference, but the totals were 0. I think I tried "vee" and "daniels" and "kings" and ran out of time. I'm off to work. if you want to see a visual of our store and where your work product resides, check out the website villageantiquemart.net. Later..... G

  68. #68
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I'll look at this
    the TOTAL at the bottom of the sheet was 0

  69. #69
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Simple fix for this
    the TOTAL at the bottom of the sheet was 0
    Let me know of issues.
    Your web site is outstanding.
    Attached Files Attached Files

  70. #70
    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'm thinking it's working perfectly. I will try again after I enter a few more days of sales.
    Glad you liked our website.
    I still haven't figured out the quote symbol (quote.jpg). Is it an icon somewhere on the page?
    I'll get back to you by Monday with results of testing the macro book v9. Ginger

  71. #71
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginny
    See this Quote1.jpg for the quote icon.

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

    Help, help. I must have made a mess. Everything worked fine after entering two days' sales. Then today, when I finished the last day and I have to write the checks, everything went berzerk. I tried to do a print screen to show you my error messages. I tried to clean everything up. I did enter a couple of new dealers (Penny, Ruthie, and Toni) but tried to be careful. Hope you are there. Shoot, everything worked so well for a couple of days. Hope this is not a major problem. Have a good weekend. Ginger
    Attached Files Attached Files

  73. #73
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginny

    This issue relates to the formatting you have in AUG 2012 worksheet...all cells down to the last row (65536) has formatting. Your massive file size is also directly related to this extraneous formatting.See this link http://support.microsoft.com/kb/305568

    This can be fixed. What's the maximum number of transactions you'd anticipate in your BEST month (2,000, 5,000, 10,000...)?

    How do you create the SEPT 2012 workbook...explain the physical steps.
    Last edited by jaslake; 09-02-2012 at 10:28 AM.

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

    Maximum items: 2000 monthly
    To create September, I delete info in the following cells:
    CC
    Name
    Description
    Inv
    Unit
    Resale

    The rest of the column information stays intact because they contain the formulas.

    I have not created September yet because I don't want to carry over any errors from August.

    Since the workbook has evolved, I do not need TABS for:
    Backup Copy
    Consign Rec'd
    wip
    budget

    The "My Lists" tab I think is something that I did not create, but maybe is automatic once I do the sorting?

    I'm home today writing checks for August and hoping that at least the calculations wound up correct for the CHECKS column.

    As always, awaiting your magic. Ginger

    A couple of days ago, I thought I had created (highlighted) all blank cells from my last row and deleted info, but I must have missed something. Possibly because there are a gazillion rows, I did not get every cell.

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

    Maximum items: 2000 monthly
    To create September, I delete info in the following cells:
    CC
    Name
    Description
    Inv
    Unit
    Resale

    The rest of the column information stays intact because they contain the formulas.

    I have not created September yet because I don't want to carry over any errors from August.

    Since the workbook has evolved, I do not need TABS for:
    Backup Copy
    Consign Rec'd
    wip
    budget

    The "My Lists" tab I think is something that I did not create, but maybe is automatic once I do the sorting?

    I'm home today writing checks for August and hoping that at least the calculations wound up correct for the CHECKS column.

    As always, awaiting your magic. Ginger

    A couple of days ago, I thought I had created (highlighted) all blank cells from my last row and deleted info, but I must have missed something. Possibly because there are a gazillion rows, I did not get every cell.

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

    Trying to clean up my file. Now getting 76 error message, path not found, when trying to run Dealers code to separate out dealers. Also getting circular reference errors. Mercy me!!

  77. #77
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Sorry I haven't gotten back to you today...been traveling the last several days (Grandson's 5th Birthday). This error
    getting 76 error message, path not found, when trying to run Dealers code to separate out dealers
    is caused by records 1031 (#CF/HD) and 1034 (#CF/RP) in your AUG 2012 worksheet. The "/" is an Invalid Character in naming of a file. The Invalid Characters are "\", "/", "?", "*", "[" and "]".

    Change the "/" to perhaps "_" and this error will go away. I've also changed the formatting on several worksheets to allow you to have up to 2500 transactions.

    I'm attaching a revised version of your August file with the above referenced changes and it seems to complete as expected. If you have issues let me know.

    PS: You WILL NEED worksheet "Backup Copy" as the code uses it. Worksheet "My Lists" is created by the code. Leave it be. The other worksheets you mentioned...feel free to delete.
    Attached Files Attached Files
    Last edited by jaslake; 09-03-2012 at 06:51 PM.

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

    Testing now. Will let you know how it goes. Ginger.

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

    Well, I created a reply, moved to a different screen, and now it's gone. So, starting over.
    I assume I can create the September workbook the way I described above?
    Is there a way to fix the column widths as shown in the AUG 2012 tab so that when the Dealer and Consignor individual sheets are created, the width is the same as the source? The worksheet files created use default widths, scrunching up some of the columns (like the description column) so I am using a lot of paper when I print.
    I finally saw the "/" example even though I looked for it before. When I looked before, I noticed that there are many "/" symbols used in the description rows and date, but I guess it doesn't matter there because that column is not used in the code manipulations. Right? I won't use the "/" in the future in the description rows. Can I leave the date as is? I occasionally get an error that I have a circular reference (not in the latest file you sent back). Is there something I have done, or can do about this, or does it matter?
    OK. I see the quote icon. Experimented but didn't figure out how to include in my message. You say you changed the formatting on several worksheets. I assume you mean the AUG 2012 worksheet rows to 2500. Right?

  80. #80
    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, forgot to say above that everything is working fine, created the Dealers and Consignor lists perfectly. No error messages.

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

    Well, I created a reply, moved to a different screen, and now it's gone. So, starting over.
    I assume I can create the September workbook the way I described above?
    Is there a way to fix the column widths as shown in the AUG 2012 tab so that when the Dealer and Consignor individual sheets are created, the width is the same as the source? The worksheet files created use default widths, scrunching up some of the columns (like the description column) so I am using a lot of paper when I print.
    I finally saw the "/" example even though I looked for it before. When I looked before, I noticed that there are many "/" symbols used in the description rows and date, but I guess it doesn't matter there because that column is not used in the code manipulations. Right? I won't use the "/" in the future in the description rows. Can I leave the date as is? I occasionally get an error that I have a circular reference (not in the latest file you sent back). Is there something I have done, or can do about this, or does it matter?
    OK. I see the quote icon. Experimented but didn't figure out how to include in my message. You say you changed the formatting on several worksheets. I assume you mean the AUG 2012 worksheet rows to 2500. Right?
    Copied this message I already sent before to see if the quote thing works. Ginger

  82. #82
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    As you can see from your post #81 the "quote thing" works. I'd temper how I use the feature...Mods get a bit out of sorts when one quotes an entire post.

    The "/", "\" issue is relative only to Column C of the AUG 2012 worksheet as we're creating File/Folders based on what the VALUES are in THAT PARTICULAR COLUMN. Files/Folder Names cannot have Invalid Characters. In naming your Dealers don't use the Invalid Characters. Of course the code could deal with that but it's a matter of discipline. If you'd like the code to deal with it we can accommodate.

    Regarding this
    I assume I can create the September workbook the way I described above
    Try it...you may (or may not) like it.

    The attached code deals with this.
    Is there a way to fix the column widths as shown in the AUG 2012 tab so that when the Dealer and Consignor individual sheets are created, the width is the same as the source?
    Attached Files Attached Files

  83. #83
    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, sorry. I thought about the ginormous quote after I sent it. Will be careful.
    I occasionally get an error that I have a circular reference (not in the latest file you sent back). Is there something I have done, or can do about this, or does it matter?
    . Latest Macro Book v9.xls replaces the last v9 you sent, right? Gonna try things out now. Will let you know. Thanks, Ginger.

  84. #84
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    This is correct
    Latest Macro Book v9.xls replaces the last v9
    If you'd like me to chase this down I'll need to see the offending file
    I occasionally get an error that I have a circular reference
    Does it matter...probably...something is amiss.

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

    OK. Next time circular reference shows up, I will try to figure it out, or send you offending file. Still working on sales slips today. So far, so good. Ginger

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

    Working magnificently. Looks like the grand total for Dealers is incorrect, but I am going to finish the rest of my sales books before I send anything to you. As my grandkids would say, "SWEET!"

  87. #87
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    As would mine (Grandchildren). Let me know of issues.

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

    Another session of entering sales slips and sorting. Works fine. Will keep you posted for rest of month. The big test will be October 1. Cheers. Ginger

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

    Well, I have done it again. Last time I entered was a 9/22/12 entry. Then I picked up entries with the first one being 9/22/12 miscellaneous jewelry 4.00. the next slip started with "will of the mill" book. i got a run time 1004 when I did last step of macro v9 (2) (create files). I tried to search for characters that did not belong, but could not find any. Everything worked fine until that last entries from part of 9/22 through 9/25. Geez. I went through 2/3 of the month just fine!! What am I doing?
    Attached Files Attached Files

  90. #90
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I ran the code twice against your current file and didn't receive any errors. What is the error message?

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

    Well, I am at the store now, so don't have exact message, but it was run time error 1004 and options given for problems said something about possibly character (/*[],etc.) and possibly path file name more than 253 characters, and other options I don't remember. I looked at the debug and it highlighted one line with yellow, but I didn't know what it meant or what to do with it, so I wrote you and sent the file. When I get home tonight, I will run all four codes again and see what happens, but I tried several times before sending you the file. When the error comes up, i will copy exactly and send to you. I am still using the macro v9 (2) that I have been using every day. More later... Thanks.

  92. #92
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    See if you can capture the line that is highlighted when the error occurs.

  93. #93
    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 ran all the codes on my file and it worked fine here at my shop. So I don't know why it is not working on the same file (from my Cloud backup) at home It is the same macro v9 (2) and the same source file. Oh well, I will keep you posted. Having a big Harvest Festival (once a Year) in our little village, so hoping sales will be good. Take care and thanks.

  94. #94
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Don't use the Cloud myself...have a 2 terabyte external hard drive for backup. Hope it clears up.

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

    Just finished the month of September. Everything went swimmingly. All the codes executed like they were supposed to. I do notice that occasionally if I make a mistake and overwrite with the correct text that the previous info that was wrong sits hidden in the cell. Now, I do an extra backspace and delete and maybe that helped. Is there a way to view what is hidden in a cell? So, cautiously, I can mark all these issues solved, but I am scared. Maybe I can leave open for a couple more weeks?? I am immensely grateful for all the work you have put into this for me. I looked up Atwood Lake. Pretty classy. Vacation home or permanent home? Cheers, Ginger

  96. #96
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I've not experienced this so can't advise you
    occasionally if I make a mistake and overwrite with the correct text that the previous info that was wrong sits hidden in the cell...Is there a way to view what is hidden in a cell?
    Yes, Atwood Lake is a pretty neat place. We bought the property when the Children were young (late 70's). At that time it was a fishing cottage we used as a "get away" place. In the early 90's I tore the old building down and put up a new building and moved in full time...and...eventually retired to the Lake.

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

    Just entered October 1-4 and everything worked fine. September finals went fine. There is one thing that would be a matter of convenience. Now, I just cut and paste or visually enter totals, but I need a list of each dealer and consignor and only their totals for the month. I use this as a check-off for my employees when they receive or give out checks and I am not there. I don't want to leave the whole manipulations for them to see, so I create a list of name, amount due or amount owed and print that for them. See attached. Is there a way to make this easier? It doesn't have to be in the big worksheet. It can be a new one on its own. Or in the same worksheet Macro Book v9 (2). I tried doing it myself by referring back and forth to cells, but holy moly, disaster.

    So, you are retired and still do all this complex work? You must like it a lot!! Cheers, Ginger
    Attached Files Attached Files

  98. #98
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger
    We could probably build something like this except for the First/Last Name piece. I've looked at the Name List on sheet Code & Autocorrect and don't see a way to cross reference the Dealer/Consignor code names to that list. So, the best I'd be able to do is create the report with the code names.

    Now, if you were to add the code names, along with First and Last Names to a sheet (or update Code & Autocorrect to include the same) I'd have a cross reference. Pick your poison.

    Yeah, I retired about 12 years ago. The last 25 years of my working career heavily involved systems development and integration (when I wasn't doing the Accounting stuff). I really enjoyed developing tools for my use and those that worked for and with me to make their jobs easier and more fun. This work I do now is an extension of that...besides, it keeps this old mind active.

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

    So, see if I understand. When I enter a name for a ticket (NAME), I should have the same thing on the CODE & AUTOCORRECT? For instance, I now have "Chaffee-DKC" on the first tab, so under CODE & AUTOCORRECT tab, I should have the same thing? I have renamed Column B in CODE & AUTOCORRECT to simply NAME and entered Chaffee-DKC. Is that what you mean? I kept CODE & AUTOCORRECT Column A named as FirstName because I use it for other purposes. See my attached sample. I could create an entirely new workbook with just the code names if that makes it any simpler. Recently, I moved all the CODE & AUTOCORRECT into its separate workbook just to make my main workbook smaller, but whatever makes it easier is my poison.
    Attached Files Attached Files

  100. #100
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Virginia

    If the Code Names in Column C of Oct 2012 worksheet are the same as the Code Names in Column B of Code & Autocorrect (and they're all there) then, yes, a Summary worksheet can be prepared by Name for the Dealers and Consignors using Code & Autocorrect as a cross reference to grab the Name.

    The Name you'll get will be what's in Column A of Code & Autocorrect along with their current month total. If you want First and Last Name then it would need to be in Code & Autocorrect

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

    When the summary sheet is done and prepared by name for dealers and consignors, I would like the names to be as they appear in Column C of OCT 2012 and therefore the same name as in Column B of Code & Autocorrect. Just confirming what you said.

  102. #102
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    That's what I refer to as the code name...such as "Abrams-C#DA" and "Soto-Lacey DLGS" (from Column C of Oct 2012).

    I'm not certain we're on the same page but if that's what you wish I've got that code done. I need to test it a bit more...get back to you.

    Here's how they'll look...is this what you want?
    Dealer.jpg
    Consign.jpg
    Last edited by jaslake; 10-10-2012 at 04:59 PM.

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

    MOST RECENT REPLY: Well, once again, I wrote a reply and then lost it somewhere, so I hope you don't get two different replies for the same thing. This is the one that counts because I re-read everything and the answer is SIMPLY that the two jpg files are EXACTLY what I want and they match Column C in OCT 2012, which is also what I want. Were the jpg files examples or is that the final product? Occasionally, I have to make a small correction after the fact, which I can do by hand on the jpg. But if they could be in excel that would be super.

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

    Been thinking about new computer and new office suite 2010. I notice you have 2010 and others. Will all this hard work you did still work on 2010 for me? I love 2003, but have conversion problems sometimes going from 2003 to 2007 (store) and am thinking they should the same.

  105. #105
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Those reports are created in Excel and will be worksheets in your Monthly file...I simply sent pictures of what they'd look like rather than attach the entire file. Attached is a new Macro Book that includes the code. Let me know of issues.
    Attached Files Attached Files
    Last edited by jaslake; 10-11-2012 at 01:33 PM.

  106. #106
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I just ran all the Procedures in Excel 2010 without issues. There are some differences but the biggest thing you'll need to get accustomed to is the User Interface but this is always an issue when upgrading Office.

    Good luck.

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

    Everything worked perfectly until I got to "Create Reports." Then I got a run-time error. See attached.

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

    P.S. I ran the report on October sales I have entered so far. Do you need that file?

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

    P.S. I ran the report on October sales I have entered so far. Do you need that file? Here is partial snapshot of the debug screen. The yellow highlight appeared on the line "With Sheets(D & C Report)

    Please Login or Register  to view this content.
    Last edited by Cutter; 10-14-2012 at 11:54 AM. Reason: Added code tags

  110. #110
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    On my platform Macro Book v10.xls runs fine on the version I have of your September file. Try the code on your September file...let me know the results.

  111. #111
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I was sitting here watching the Browns losing yet again and my mind wandered to your October file issue.

    You'll need to add two worksheets to the file before you run the Reports Code...D & C Report and Consign Report. Sorry I didn't tell you that previously.

    AddSheets.jpg

    Let me know if this fixes the problem.

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

    Everything worked super. Once again, I wandered off the ExcelForum site to check something and my previous note I was creating to you disappeared. Will I ever learn; I either lose the note or it submits twice. Anyway, will enter a couple of sales books tonight and keep you posted. Go Browns! As for me, I am waiting for basketball....See ya. Ginger

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

    Everything's working fine. In the September "Create Files," the format from the original (OCT 2012 tab) (column widths) was retained in the created files. When I run October, the column widths do not retain the original format. Is there something I can run so that the format retains the original (from OCT 2012 tab). Otherwise, working super. Finally getting rain here on the coast. One day last week we set records (95 degrees) and the next day it was 75, and then we had a 5 point earthquake about 2 hours away that rocked us in our sleep. The dogs went nuts! Your Browns need to fire it up! Ciao, Ginger

  114. #114
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I'm not sure what this is about
    When I run October, the column widths do not retain the original format. Is there something I can run so that the format retains the original (from OCT 2012 tab).
    I'll try to recreate the issue if you'll upload the offending file. Please let me know what version of Macro Book you're running.

    Regarding this
    Finally getting rain here on the coast. One day last week we set records (95 degrees) and the next day it was 75, and then we had a 5 point earthquake about 2 hours away that rocked us in our sleep. The dogs went nuts!
    As I've said, I've spent considerable time in your "God's" country and, while I sympathize, ya gotta admit, it's not too bad.

    Regarding this
    Your Browns need to fire it up!
    We just got a new Owner and a new front office...a new Coach is probably in the winds...ah well...it builds character...

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

    All I need is the "Create Reports" files to match the column widths of the original first tab on my workbook (in this case OCT 2012). Seems you fixed this once before and that is why SEP 2012 is OK. See two files attached.
    Attached Files Attached Files

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

    Macro Book v10.xls is the current macro I run. Works great.

  117. #117
    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 think I should be referring to the "Create Files" not the "Create Reports." The files I am talking about are the ones you created code for making a folder called DEALER and CONSIGNMENTS. And in those folder are lists of the dealers reflecting the number for each month, and the same for consignments. So we have ESCOTO-DKE OCT 2012.xls, ESCOTO-DKE SEP 2012.xls, and ESCOTO-DKE AUG 2012.XLS (for example) so far. Sorry, I called it "Create Reports" and I believe it is "Create Files."

  118. #118
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    If you don't feel up to this simply say so.
    There's a single line of code to add to Sub CreateMonthlyFiles(). It's down toward the bottom of the procedure.
    Please Login or Register  to view this content.
    Put in that line and run...let me know the results.

  119. #119
    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 did this. Got an error "Compile error Next without For" Had trouble finding the module, but this is what I did. Maybe I was in the wrong place?? Couldn't find a way to "save as" so I could name it v11 so I saved and just renamed. Interesting. Ciao for now....Ginger
    Please Login or Register  to view this content.
    Last edited by vtaylor; 10-28-2012 at 03:43 PM.

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

    Didn't look right above (all the spacing to the left). I am trying again. If it all moves to the left in this note, it isn't that way in the visual basic. Ginger

    Please Login or Register  to view this content.
    Last edited by jeffreybrown; 10-28-2012 at 04:30 PM.

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

    Please Login or Register  to view this content.
    Last edited by vtaylor; 10-28-2012 at 03:39 PM.

  122. #122
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Ya GOTTA use CODE TAGS on ANY code you post...does not matter if it's one line or 100 lines...this is an absolute rule of the Forum. Please go back to your posts and edit them to do so.

    I'll post the entire revised Macro Book v10.xls tonight (after you've done the above).

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

    Please Login or Register  to view this content.
    Last edited by vtaylor; 10-28-2012 at 03:40 PM.

  124. #124
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

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

    @vtaylor,

    We would love to continue to help you with your query, but first, before we can proceed, please see the forum rules about using code tags and adjust accordingly...

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    HTH
    Regards, Jeff

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

    Well, I tried with the CODE TAGS. Seems like I made a big mess. Ginger

  126. #126
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    As I said...we're gonna learn you.

    Go to your post #123. Bottom right...select Edit. In the Edit Screen highlight the CODE Lines (these lines)
    Please Login or Register  to view this content.
    When you have the CODE lines highlighted click on the pound sign (#) button in the Icons at the top of the frame. Code Tags.jpg

    Click Save...it's as simple as that.

  127. #127
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Your Code Tags look great. Here's the revised Macro Book to accomplish this
    files to match the column widths of the original first tab on my workbook
    Let me know of issues.
    Attached Files Attached Files

  128. #128
    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 replied to the last post that everything worked perfectly for November. I saw the post when I logged on just now and then it disappeared. Mercy Me! Anyway, been away from computer moving quite elderly mom from Santa Rosa, CA to here. Since moving her here, I will have to do most of my work on my laptop so I can stay at her house. I am going to try to move all the macros I use for getting around the worksheet to my laptop; wish me luck. One thing I wanted to repeat in case you did not get the post that disappeared was I think of you every day when I am doing my "stuff" on the worksheet. If I don't etalk to you before, have a wonderful Holiday. Cheers, Ginger

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

    MCTAATAAGN. Puzzle for you. G

  130. #130
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    Thanks for the Holiday wishes...back at 'ya.

    Been through the Mom (may She rest in peace) scenario myself several years ago. Fortunately I had a lot of help (1 of 9 living Sibs) and my 3 living Sisters did most of the heavy lifting.

    Regarding this
    I am going to try to move all the macros I use for getting around the worksheet to my laptop
    All should be rather straight forward. The only issue may be those Macros that are in your Personal.xls. Don't use Personal.xls myself but I'm certain there are many on this Forum that can help you with that.

    When you
    think of you every day when I am doing my "stuff" on the worksheet
    please think positive thoughts for Family issues...a really small prayer will be appreciated.

    Merry Christmas...best wishes for the New Year...let me know how I can help.

  131. #131
    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'm hanging on to you one more month to see if December sales go as smoothly as November did, then I will do the official thing and marked this solved. Large prayers coming your way. Cheers. Ginger

  132. #132
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Thanks Ginger...truly appreciate it. Best Wishes...let me know how I can help.

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

    Tried transferring the macros like you showed me once before. I think it worked! More later after I test it out. Ho, ho, ho.....Ginger

  134. #134
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Merry Christmas...

  135. #135
    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: Still out there? December 2012 and January 2013 worked fine. Now in my first try at February sales 2013 I get a run time error 1004. Are you still up for helping me? Ginger

  136. #136
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

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

    Hi Ginger

    I'm still around...haven't croaked as yet...

    I believe this Thread has been beaten to death...it's time you started a new one. Ask a Moderator to mark this Thread as Solved. Start a new Thread with your new issue...PM me with a link...I'll look at it.

  137. #137
    Forum Contributor
    Join Date
    08-22-2012
    Location
    nj, us
    MS-Off Ver
    Excel 2010
    Posts
    163

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

    yep, I still here

  138. #138
    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: Are you there. Need help if you can. Thanks, Ginger

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

    This thread is solved.

+ Reply to Thread

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