Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Tagging data to copy to different spreadsheet

    I'm not sure this can be accomplished, but I'm hoping there's a better way of creating a new customer list from the mailing list I use to send out our lawn care contracts. (I've attached a dummy file) When people send their signed contracts in after our mass mailing in February, it's my job to enter all the needed information (name, address, etc.) onto a new spreadsheet (labeled "20XX Customer List"), which essentially duplicates the information from the "20XX Mailing" spreadsheet. I would like to accomplish two things - ONE: Somehow, as contracts are returned to us, tag the names in the "20XX Mailing" file to automatically copy over to the "20XX Customer List" list so I don't have to retype everything, saving time and the chance of errors. I double-check every entry so it really is becoming very time consuming when we get upwards of 1000 customers. TWO: Use the "20XX Customer List" for the following year's mailing. I've actually tried doing that before without much success and have just been copying everything into a blank sheet.

    I'm not sure if I'm asking too much but I won't know until I do. Thanks in advance for your help
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thanks so much for your reply. When I implement your suggestions I will let you know how it works for me. Your suggestion on using one sheet as a mailing and customer just may be feasible. I will let you know after I can study it and put your suggestions to use. Thanks again so much! More later...........

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Okay, nearly a year later and I'm now ready to implement this document as we are getting in this year's batch of contracts. The process seems to work as I need it to, saving re-entering names and addresses, etc., which greatly improves the margin of error. I used the sheets on the file that was attached in the last reply and just copied my 2014 mailing data into the "2013 Mailing". However, it appears that when I rename the tab from "2013 mailing" to "2014 mailing" it loses the ability to bring the names to the (now) "2014 customer list". Is that because I have to establish a new link?

    Also, I have found that it's very easy to inadvertently put a checkmark in the field that choses a name to be transferred to the customer list. I'm concerned about getting names in the customer list that shouldn't be there. Is there another way to select the names to update in the customer list? (I hope I've explained it clearly).

    Thanks in advance!

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,157
    Thanks
    96
    Thanked 320 Times in 309 Posts
    Hi k32rem

    ..The existing vba code refers directly to the 2013 sheetnames.
    Maud could adjust the vba code to refer to 'codenames' for the sheets, which would mean you could then rename the actual sheet tabs to "2014 xxx", 22015 xxx" etc without having to update the code.

    Maud could also give you a double-click routine which could place (or remove) a selection tickmark for the mail list.

    zeddy

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Sounds like great ideas, on both counts. Can you have her do it, or do I need to contact her? Thanks for the quick reply!

  6. #6
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    I had a feeling if I said "she" it wouldn't be right! Sorry about that! And thanks for the input on modifying the files for me. It's the end of my work day, so I will look at what you did tomorrow and let you know how it worked and if I have any questions. You all are the greatest!!

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Okay, I like the modification for picking the customers for the new list; not so likely to accidentally select a name that shouldn't go there. And good that I can now change the sheet names without losing the functionality when updating the customer list.
    So I started with the 2014 list since 2013 is "in the books" and I'm builiding my customer list for this season. I opened the new file you sent me and pasted my 2014 data into the renamed 2014 mailing list (as instructed) and saved the new file with a different name. All appeared to be going well until I used the "residential" sheet to create a contract and realized it was picking up the data from my original sheet in the old file that I copied to the new file. What did I do wrong? Other than that, everything works perfectly.
    Thanks!

  8. #8
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Okay, for some reason I can't find where I can attach a file for you to open. I opened the last revised file you attached. I played around with it before going ahead with copying all my data into it. I double-clicked on a couple names to get the green checkmark to add them to the new customer list. It did update. However, when I went to the "Residential" tab, I'm now getting these errors in the fields where data should be - "#VALUE!" "#NAME". All I know is these are errors I see when I've made a mistake in a simple formula. I obviously don't know enough about what you did to create these worksheets, so all I can do is refer it back to you. And I've looked and looked and can't find where to attach a copy of the actual file for you to look at.

  9. #9
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Found it!
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Okay, now I am getting the error shown on the attachment (I did a "print screen"). I can select records to update properly to the new customer list, but the contract sheet (residential) isn't working properly when I select a name to insert into the contract.
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Maud or Zeddy, have I lost you? Will I need to go back to entering the information manually for the time being?

  12. #12
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Okay, I remembered something from a few years ago when I started a new season and had to find out how to redirect the formula in the "residential" contract sheet to the right database. Using that information, I went to "Insert, Name, Define" and changed the database year to 2014 and now everything appears to be working, using the "CustList_Rev3" spreadsheet from a week or two ago. If I run into anymore glitches, I'll let you know. Thanks!

  13. #13
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Okay, everything was working fine until I updated the customer list a second time. What I have to do after updating the customer list (and what I did after doing that the first time) is put and "X" in the fields on the new customer list to designate which treatments our customers get (ES, LS, SS, FS). When I updated the list a second time after selecting more names from the mailing list, I lost the edits I made in the "ES, LS, SS, FS, AER, LIME and CALL FIRST/NOTES" columns. Is there a way to keep this from happening? This is working so well, otherwise, I hate to lose this new functionality I have. Maybe have to put this data in the Mailing List sheet so it transfers over like everything else?

  14. #14
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Would I be able to insert columns I have in the new customer list that correlate with the mailing list and add the info before updating the customer list? I would essentially have duplicate sheets then. Would I mess up formulas by being that simplistic?

  15. #15
    2 Star Lounger
    Join Date
    Sep 2001
    Posts
    185
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Okay, looking forward to hearing from you soon....

Page 1 of 2 12 LastLast

Posting Permissions

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