Results 1 to 2 of 2

Thread: cut/paste scripting - copy a range then paste to a destination range that matches to row/column address criteria - INDEX/MATCH but cut n paste
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default cut/paste scripting - copy a range then paste to a destination range that matches to row/column address criteria - INDEX/MATCH but cut n paste

    I'm struggling with some simple copy/paste scripting in Excel/Sheets. I'm a complete scripting newbie. Advanced at Excel/Sheets.
    I have a "Today" data entry sheet. I have a "Program" sheet that stores data in a column under a Date header. I want a macro/script that will:

    1. cut data from an "Actual" range on Today for a specific exercise
    2. search the date row in "Program" to Match whatever date is on the "Today" data entry page for the destination column address
    3. search the exercise category column in "Program" to Match whatever exercise is on the "Today" data entry page for the destination row address
    4. Finally paste source range from Today to destination "Actual" range on Program to cell address identified in Step 2 and 3
    5. Repeat 20 times for different exercises

    Essentially I want to INDEX/MATCH in reverse. Rather than return a cell in INDEX/MATCH, I want to copy to a cell.
    Simplified example Sheet is here with additional notes embedded: https://docs.google.com/spreadsheets...it?usp=sharing
    Full sheet is here but rather complex: https://docs.google.com/spreadsheets...it?usp=sharing

    I need eventually in Sheets but I'm sure I can figure out translating from VBA if easier.

    Huge thanks in advance. This has been making me crazy.

  2. #2
    New Member
    Join Date
    Dec 2010
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cut/paste scripting - copy a range then paste to a destination range that matches to row/column address criteria - INDEX/MATCH but cut n paste

    I figured it out. Not the most elegant solution but it works. The "var col = s.getRange(4,1).getValue();" is just a MATCH function. I'd still prefer to have the row lookup without hardcoding the row number but this works until I add stuff. The bit at the end just resets the data entry spaces to match the "recommended" amounts by formula.

    Code:
    function MyFunction() {
      var ds = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Program');
      var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Today');
      var col = s.getRange(4,1).getValue();
      
      //Sq w/ Belt
      s.getRange('B11:G11').copyTo(ds.getRange(11, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //2ct Paused Squat
      s.getRange('J11:O11').copyTo(ds.getRange(16, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //Pin Squat
      s.getRange('R11:W11').copyTo(ds.getRange(21, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //3-0-3 Tempo
      s.getRange('Z11:AE11').copyTo(ds.getRange(26, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //Sq no Belt
      s.getRange('AH11:AM11').copyTo(ds.getRange(31, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
     
      
      //1 Ct Paused Bench Press
      s.getRange('B19:G19').copyTo(ds.getRange(37, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //Overhead Press w/ Belt
      s.getRange('J19:O19').copyTo(ds.getRange(42, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //Close Grip Bench Press
      s.getRange('R19:W19').copyTo(ds.getRange(47, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //2 Ct Paused Bench Press
      s.getRange('Z19:AE19').copyTo(ds.getRange(52, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //3 Ct Paused Bench Press
      s.getRange('AH19:AM19').copyTo(ds.getRange(57, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //3 Ct Paused Bench Press
      s.getRange('AP19:AU19').copyTo(ds.getRange(62, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
      
    
    
      //Deadlift w/ Belt
      s.getRange('B27:G27').copyTo(ds.getRange(68, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //Rack Pull - mid shin
      s.getRange('J27:O27').copyTo(ds.getRange(73, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //2 ct Paused Deadlift
      s.getRange('R27:W27').copyTo(ds.getRange(78, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      
        //Pendlay Rows
      s.getRange('Z27:AE27').copyTo(ds.getRange(83, col), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    
    
      //get formula back in cells to overwrite manual input
    
    
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('B11').activate();
      spreadsheet.getCurrentCell().setFormula('=IF(b9="","",b9)');
      spreadsheet.getRange('B11:G11').activate();
      spreadsheet.getRange('B11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('J11').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('R11').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('Z11:AE11').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('AH11').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('B19').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('J19').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('R19').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('Z19').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('AH19').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('AP19').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('B27').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('J27').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('R27').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('Z27').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
      spreadsheet.getRange('B11').activate();
      spreadsheet.getRange('B11:G11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
    };

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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