+ Reply to Thread
Results 1 to 8 of 8

how to save - not just copy - certain data into another sheet

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    Northampton, England
    MS-Off Ver
    excel 2016
    Posts
    64

    how to save - not just copy - certain data into another sheet

    Oakley Invoice sample.xlsmHello all
    I have a sample workbook attached - that I will be using for invoicing.
    I kind of thought it would be jolly useful if I could save the following detail from each invoice to a separate sheet:
    - Invoice Number
    - sales price
    - cost price (which I haven't got a column for that in my invoice sheet yet)
    - nominal code (which again I haven't got a column for that yet)

    that way I can then do reports on our invoice sales automatically?!

    obviously after each invoice I will click that clear button and it will delete all the data out of the invoice - so I need it to save the data not just copy it!

    if that can be done - would be great!

    I have attached a sample copy.

    many thanks in advance!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: how to save - not just copy - certain data into another sheet

    Add a new button to your invoice sheet
    Add a new sheet to your file, call it "Log"
    Put these titles in Row1: Invoice Nr Sales Price Cost Nominal Code of the Log Sheet

    Add this code to the new button

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    12-19-2012
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: how to save - not just copy - certain data into another sheet

    Hi Oakley,

    It would be better if the sheet was finished (all columns in place) for soemone to help. Where do you want the data to go? i.e. into external workbook or are you planning to add another worksheet to the invoice workbook.

    Regards
    Steve

  4. #4
    Registered User
    Join Date
    09-27-2012
    Location
    Northampton, England
    MS-Off Ver
    excel 2016
    Posts
    64

    Re: how to save - not just copy - certain data into another sheet

    Excellent that's exactly what I want it to do.....but I cant get the cost to work!? I have uploaded the workbook again with the cost column in - obviously I want it to show the total cost ("P28")
    also - if its not too much bother I would like it to show the date of the invoice on the Log sheet too.
    I have decided I don't need the nominal code column - as ive just realised every invoice might not be all the same nominal code!
    Attached Files Attached Files

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: how to save - not just copy - certain data into another sheet

    I have amended the code as requested. See attached. Need to use PasteSpecial Values as the info in the cost is a sum formula.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-27-2012
    Location
    Northampton, England
    MS-Off Ver
    excel 2016
    Posts
    64

    Re: how to save - not just copy - certain data into another sheet

    EXCELLENT that works just fine! MANY THANKS!
    just one small point - im sure its probably v simple but.......im no wizard on VBA yet!! is there anyway that when I click the 'clear data' button it will add 1 to the invoice number?

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: how to save - not just copy - certain data into another sheet

    Add this line of code to your code for the clear button

    Please Login or Register  to view this content.
    Thanks for the Rep!
    Last edited by alansidman; 01-13-2014 at 02:57 PM.

  8. #8
    Registered User
    Join Date
    09-27-2012
    Location
    Northampton, England
    MS-Off Ver
    excel 2016
    Posts
    64

    Re: how to save - not just copy - certain data into another sheet

    thankyou!

+ 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. need to save sheet as a copy every time sheet is up dated
    By superchew in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-24-2013, 06:01 PM
  2. Replies: 4
    Last Post: 09-03-2013, 09:56 AM
  3. [SOLVED] Copy Data and paste it in new sheet and save new sheet as value of cell a2
    By VKS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2013, 08:55 AM
  4. Copy cells to new Sheet and Save Sheet
    By DadaaP in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2013, 10:34 AM
  5. Replies: 0
    Last Post: 04-28-2012, 06:06 PM

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