0

I am trying to duplicate a working script from Google Sheets into Excel. The goal is to copy the second row of the active sheet and paste the formatting, data validation and formulas into a new row above. This is intended to place a new (formatted with formulas and data validation) in row 2 below the headers.

Here's the google script

// global
var ss = SpreadsheetApp.getActive();

function addFirstRow() {
    var firstRow = 2;
    var sh = ss.getActiveSheet();
    var lCol = sh.getLastColumn();
    var range = sh.getRange(firstRow, 1, 1, lCol);
    var formulas = range.getFormulas();
    sh.insertRowsBefore(firstRow, 1);
    var newRange = sh.getRange(firstRow, 1, 1, lCol);
    newRange = sh.getRange(firstRow, 1, 1, lCol);
    newRange.setFormulas(formulas);
}

Here's a video of the google sheets in action https://youtu.be/OCinWCApypw

Screenshot

Screenshot

Community
  • 1
  • 1
jdabramson
  • 1
  • 1
  • Refer this link, I think it addresses the same issue and will be helpful https://stackoverflow.com/questions/39765110/google-script-convert-sheet-to-xlsx – 1986G1988 Jun 13 '18 at 12:08
  • The macro should look similar to this one. However, I don't know how to program it to copy row 2 and insert an empty row in its place thus shifting all rows down. `Sub Copy_One_Row_Below() With Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 16)) .Offset(1).Insert shift:=xlDown .Copy .Offset(1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats .Offset(1).PasteSpecial xlPasteFormats Application.CutCopyMode = False End With Cells(ActiveCell.Row + 1, 2) = "=" & Cells(ActiveCell.Row, 2).Address & "+1" End Sub ` – jdabramson Jun 13 '18 at 14:02

1 Answers1

0

So you just need this or I didn't understand well ?

   Sub Test()
    Rows("2:2").Insert Shift:=xlDown
    Rows("3:3").Copy
    Rows("2:2").PasteSpecial xlPasteAll
    Application.CutCopyMode = false
   End Sub
BenderIO
  • 411
  • 4
  • 11
  • This is close.. How does one insert the row with the formatting, data validation, and formulas while omitting the content. i.e. the inserted row should be blank. – jdabramson Jun 13 '18 at 14:46
  • `xlPasteFormulasAndNumberFormats` instead of `xlPasteAll` should work – Vincent G Jun 13 '18 at 14:49
  • Vincent is right, you can replace the parameter "xlPasteAll", with what you need [here](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlpastetype-enumeration-excel) . If you need multiple parameters you can retype the line each time as needed. In your exemple `Rows("2:2").PasteSpecial xlPasteFormulasAndNumberFormats` and next line `Rows("2:2").PasteSpecial xlPasteValidation` – BenderIO Jun 13 '18 at 15:32