+ Reply to Thread
Results 1 to 9 of 9

Data storage

  1. #1
    Registered User
    Join Date
    06-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    86

    Data storage

    Hi there,
    I have created an invoice template, everything works perfect, but every time that I need a new invoice, I have to clear all data.
    Question. "NOW THE WHOLE WORLD IS QUARANTINE What do I need to learn in order to use the same template with a storage data, in other words "have like a SUBMIT button, then store data and clear the template" Any website, videos.
    Any answer will be helpful
    Thank you and stay well.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Data storage

    Copy your Template and hide a copy.

    When you need to clear it.

    Delete your sheet

    Unhide the hidden sheet

    Create a copy

    Hide your template.


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 04-03-2020 at 07:18 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    06-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    86

    Re: Data storage

    Thank you.

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Data storage

    Add a new sheet "Master File" with column headers corresponding to the input fields on the template.
    Turn on the macro recorder and manually copy/paste the template data to the appropriate column on the master file sheet.
    Select the template input cells and clear them.
    We can help with finding the next row for copy, etc.
    Ben Van Johnson

  5. #5
    Registered User
    Join Date
    06-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    86

    Re: Data storage

    Well, I learned from your reply that I must learn macro, any place you could recommend ?
    Thanks for your reply.

  6. #6
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Data storage

    Ahh. You do not know any VBA.

    Ok I will talk you through this so you have a good solution

    Create a workbook containing three Sheets.

    Sheet 1: Your Template called "Template"
    Sheet 2: Empty Sheet called "Clear"
    Sheet 3: An Empty Sheet Called "Invoice"

    Right Click on the name "Clear" at the bottom of Excel and select Tab Color and select Red.
    Right Click on the name "Clear" at the bottom of Excel and select view code

    Paste this code into the module that opens and close it.
    Thats it you are ready to go.

    Select your sheet "Invoice" and type something in cell A1.
    Now select your tab "Clear". Did anything happen?

    Again.

    Select your sheet "Invoice" and type something in cell A1.
    Now select your tab "Clear". Did anything happen?


    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    86

    Re: Data storage

    Hi, thank very much for your time and patience, I followed your steps and this happened.
    1. It cleared the sheet, except DATE and Invoice # cells (not a big deal). It worked great.
    2. It delete the sheet named TEMPLATE.
    3. I cannot access to the sheet named INVOICE.

  8. #8
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Data storage

    Ok.

    1. It did not clear the sheet.
    It deleted your sheet "Invoice" and replaced it with the Template
    So the date and Invoice number must be in your Template.
    You should modify the macro to increment the Invoice Number.


    2. It did not delete the sheet "Template" that sheet is hidden.
    Right Click on a sheet name at the bottom of excel and select unhide.
    Select "Template" and then OK.

    3. Not understood. Your sheet name is "Invoice"
    Where did the sheet "INVOICE" come from and why can you not access it?


    I have modified my sample for you.

    The Invoice number is in B1.

    Click on "Clear" and watch the Invoice number increase as the Invoice is Deleted and a new Invoice is created.

    The Question is do you want the Invoices deleted.


    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by mehmetcik; 04-04-2020 at 09:59 AM.

  9. #9
    Registered User
    Join Date
    06-06-2010
    Location
    USA
    MS-Off Ver
    Excel 2016
    Posts
    86

    Re: Data storage

    It worked, thanks again for help and support.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. data storage
    By david90 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2012, 03:37 PM
  2. Data storage and access
    By firefly2k8 in forum Excel General
    Replies: 1
    Last Post: 03-05-2010, 12:39 PM
  3. Data storage in Excel
    By dhruvan in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-26-2007, 10:44 PM
  4. Data Storage
    By gregory.barrett in forum Excel General
    Replies: 3
    Last Post: 03-30-2006, 05:25 PM
  5. [SOLVED] External data storage
    By Jzz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2006, 06:10 AM
  6. newbie needs help with data storage
    By mrclox in forum Excel General
    Replies: 0
    Last Post: 11-22-2005, 05:52 PM
  7. [SOLVED] data storage from one worksheet to another
    By coal_miner in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2005, 12:06 PM

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