+ Reply to Thread
Results 1 to 12 of 12

Automated worksheet copy, rename based on cell results

  1. #1
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Automated worksheet copy, rename based on cell results

    Hi. I am building a workbook for a friend to use with his business, and am starting to understand why professionals charge so darn much to do it! Anyway, the data we are tracking is all in one xml file, which i have mapped to 3 different worksheets(customers, invoices, inventory). On the customers and inventory sheet I have an interface for creating a new customer/product/invoice. I used the macro recorder to make the macro's to do these three things, but could use help on a couple of other functions as I don't actually know VBA. I need to be able to automate editing of existing records by having a button to press on the each sheet that will open an input box that asks the user which invoice number, customer number or product number he would like to edit, then copy that record to the interface for editing, then another macro to replace the existing data with the newly edited data. It would also be wonderful if when creating or editing an invoice it could create a copy of the interface worksheet and rename it as the invoice number. The first row of each datasheet is blank, and each of the interfaces have formula's for importing the information copied to row 1 from the data tables. the second row of each datasheet contains formulas for importing data from the interface. Any help would be terrific...I know you guys are excel gods and I will forever be in your debt if you can help me out....thanks in advance, I'll be studying my butt off until I figure this out

    EDIT-I added the workbook as attachment, minus the macros..thanks again guys
    Attached Files Attached Files
    Last edited by humboldtguy; 07-29-2009 at 05:41 AM. Reason: attachment

  2. #2
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    764

    Re: Automated worksheet copy, rename based on cell results

    Hi
    i have coded 2 macros in the attached workbook - to edit and to save edited record for customer sheet. with minor changes it could be applied to other sheets too
    Ravi
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: Automated worksheet copy, rename based on cell results

    Thanks ravishankar, you are awesome....i think i understand how to change that to suit my needs, i really appreciate the help...thank you!

  4. #4
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: Automated worksheet copy, rename based on cell results

    hey ravishankar, thanks again for the help, one thing that i can't figure out is on the replace record code...it does not replace the original record....i need row 2 to be copied and its values pasted over the top of the record with the same customer ID number, sorry for being a pest...the edit customer macro is working perfect and if i can get this part right i will be pretty much finished
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    764

    Re: Automated worksheet copy, rename based on cell results

    Hi
    Perhaps I should have mentioned that both macros pull data to row 3 and on editing it gets replaced.
    Ravi

  6. #6
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: Automated worksheet copy, rename based on cell results

    oh...ok...I didn't pick up on that, how would i go about making the new information replace the old information that is in the table? The formula's on row 2 of the sheet refers to the data that is in the user entry grid, so what i would like to do is copy row 2, and paste special(values) over the existing record with the same customer ID number. Column 1 of the table has the customer ID numbers.

  7. #7
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    764

    Re: Automated worksheet copy, rename based on cell results

    Hi
    On running macro1, it pulls records related to customer ID entered, in row no 3. the font colour of row 3 is white (pl change it to readable colour). Edit row 3 and run macro 2. It replaces the original record and deletes row 3 and gives message as complete. I have inserted row 3 in your book. Hope that clears the confusion.
    Ravi

  8. #8
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: Automated worksheet copy, rename based on cell results

    I'm don't think we are on the same page. I need the macro to copy all of row 2, find the row in the customers table(which starts at row 25) in which column A matches the value of cell A2, then paste row 2's values over the top of that row. If it just goes to row 3, then the user would still have to perform the needed function himself, which is replacing a customer record with an updated customer record. I hope you can understand what I am saying, I really appreciate the help.

  9. #9
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    764

    Re: Automated worksheet copy, rename based on cell results

    Hi
    Attached is the modified macro. Click edit record button: it asks for customer ID. On entering that data, it searches from row 25 till the end. Matching records are copied to Row 1 which is reflected in the table as well as row 2. Now you can edit data in row1 and press second button. which will copy row1, replace the records in appropriate row, indicates completion with a message and clears row1.
    RAvi
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: Automated worksheet copy, rename based on cell results

    hi ravishankar,

    That isn't working...not sure what exactly is going wrong, but what is happening is that the original record is being deleted, but not replaced with the new information? I am in over my head on this one, thanks again for all your efforts, the macro that is loading the information to row 1 is perfect btw

  11. #11
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    764

    Re: Automated worksheet copy, rename based on cell results

    Hi
    I have fixed the problem
    Ravi
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    10-01-2008
    Location
    Eureka, CA
    Posts
    161

    Re: Automated worksheet copy, rename based on cell results

    Hi Ravi, thanks again for all the help but the 2nd macro still isn't working correctly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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