+ Reply to Thread
Results 1 to 10 of 10

How to save data on next row

  1. #1
    Registered User
    Join Date
    03-26-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    11

    Question How to save data on next row

    Hello everyone! I'm super new to macros and scripts so please bear with me.

    Scenario:
    a. I have some data on a spreadsheet where the function that I'd like to happen are the ff:
    - A save button (image), when clicked, saves the data from specific cells to a new sheet
    b. All the next data that will be saved should NOT overwrite the previous row's data (right now, the save button works but when I save it again with a different entry on the cell, the new value overwrites the previous value)
    c. The next saved data should move down to the next available row

    Right now, this is the only macro script I was able to create (this is literally my first day on Macros!):

    function Test1() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('M2').activate();
    spreadsheet.getRange('B6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.getRange('N2').activate();
    spreadsheet.getRange('B7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.getRange('O2').activate();
    spreadsheet.getRange('B8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.getRange('P2').activate();
    spreadsheet.getRange('G7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }


    Any help will be greatly appreciated!

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to save data on next row

    Does that code actually copy to another sheet or is it copying to the same sheet?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    03-26-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    11

    Re: How to save data on next row

    Hi Norle!

    I did a new code which now copies to a new tab within the sheet:

    function James1() {
    var spreadsheet = SpreadsheetApp.getActive();
    spreadsheet.getRange('B6').activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Dump'), true);
    spreadsheet.getRange('A2').activate();
    spreadsheet.getRange('Form!B6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Form'), true);
    spreadsheet.getRange('B7').activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Dump'), true);
    spreadsheet.getRange('B2').activate();
    spreadsheet.getRange('Form!B7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Form'), true);
    spreadsheet.getRange('B8').activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Reference list'), true);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Dump'), true);
    spreadsheet.getRange('C2').activate();
    spreadsheet.getRange('Form!B8').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Form'), true);
    spreadsheet.getRange('F7').activate();
    spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Dump'), true);
    spreadsheet.getRange('D2').activate();
    spreadsheet.getRange('Form!F7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    };

    What I really can't figure out is what do I need to add so that new entries do not overwrite previous ones and just place the new values on the next available blank row.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to save data on next row

    You do not need all that code to do this.

    I can distil your original code to this.
    Please Login or Register  to view this content.
    Now, in your new code you appear to be referring to 3 different sheets and it's not really clear what you are copying from which sheet and where you are copying to.

    Can you post in words what ranges/sheets you are copying/pasting from/to?

    P.S. Are you only copying/pasting values?

  5. #5
    Registered User
    Join Date
    03-26-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    11

    Re: How to save data on next row

    Sure! And yes, I'm only trying to copy and paste values

    A. Copy values from specific cells of "Form" sheet to "Dump" sheet

    Here's the spreadsheet file I'm trying to work on: 1gdVFQ_JXW3SpPnw1Trx-J-9putO00jJ2vPc09KrdgzY

    Really sorry if I'm confused!

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to save data on next row

    The link to the spreadsheet didn't work.

    P.S. Did you get the original code by recording a macro?

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to save data on next row

    I think I've figured out the references but it should be easy to change them if they aren't right.

    This will copy B6, B7, B8 and F7 from the sheet 'Form' to the next empty row in columns A, B, C and D respectively in the sheet 'Dump'

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-26-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    11

    Re: How to save data on next row

    OMG!!! You are a life saver!!! It works!!! I'll try to search how the script works but its brilliant!!!

    Just a few follow up questions though:

    1. How do I add additional values to get from the Form tab?
    2. Do you have any recommendation on how to create a "Clear" button, which resets the form once it's saved and the put default values in once cleared?

    Thank you so much Norle!!!

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: How to save data on next row

    This is where the values to copy are being set, so if you need to add any other cells you would do it here.
    Please Login or Register  to view this content.
    For example, to add the range 'A7:A10' you could use this.

    Please Login or Register  to view this content.
    Note, that if the range you are adding has more than one cell you add the spread operator ... in front to get the values.

    For clearing the values you might want to look at getRangeList.
    Please Login or Register  to view this content.
    Setting defaults could be trickier depending on what the defaults actually are.

  10. #10
    Registered User
    Join Date
    03-26-2018
    Location
    Philippines
    MS-Off Ver
    2013
    Posts
    11

    Re: How to save data on next row

    Got it! The cells work now! You are the best! Thank you Norle!

+ 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. 1 macro to Save as w file name + progressive number & another save as for next data entry
    By ILoveStMartin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-08-2019, 06:38 PM
  2. [SOLVED] Trying to save input data that is formulated from a sheet where the data is erased daily
    By Chris1972 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2018, 10:11 PM
  3. [SOLVED] Macro-Launch Notepad from Excel and save data in notepad and save with naming conventio
    By hjaspaul in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-01-2016, 09:19 PM
  4. Replies: 1
    Last Post: 04-29-2016, 11:55 AM
  5. Disable Save As and Save - enable Save via button
    By LampCommandr in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-07-2011, 05:42 PM
  6. How to diasble save and save as menu but allow a save button
    By hon123456 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2006, 04:55 AM

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