Copy the last row of current sheet to a new row of a specified sheet-CodePudding

In Google Spreadsheet, I need a macro that copies some content(B-D, G-K) of the last row of the current sheet to the same range of the first empty row of a specified sheet. Please kindly provide detailed instructions, I am new to coded macros.

If helps, here is a link to my file https://docs.google.com/spreadsheets/d/1x2b3YfjSyQSU0I6p0PT1mFDXpX4O11rCQBpL6r4fFaQ/edit?usp=sharing and description of what i seek.

This company moves cars from one city(city A) to another city(city B). And we have a sheet for every city that they start from. But sometimes a car is going to go through a certain city(city C). So it will go A to C to B. So we (manually) add A to C to our list, but the car is also going C to B. Naturally, all the data on that car will be the same on list C as it was on list A. So i would like this: I press a combination for a macro and it copies ranges B-D and G-K of the selected row of the current sheet to the first empty row of a specified sheet.

If needs be, i could translate the spreadsheet into English. What I have so far: It workds except it copied the entire row not my ranges (B-D and G-K):

enter code here
function myFunction () {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var SourceSheet = ss.getActiveSheet(); 
var TargetSheet = ss.getSheetByName("Красноярск");
var LastRow = TargetSheet.getRange(2,3,10000,1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() 1;
var SourceCell = SourceSheet.getActiveCell();
var SourceRow = SourceCell.getRow();
var SourceRange = SourceSheet.getRange(SourceRow, 1,1,12);
SourceRange.copyTo(TargetSheet.getRange(LastRow,1,1,4));
}

I tried formulas (index, vlookup, filter), but it does not help collecting rows from 20 different sheets
I tried formulas with checked boxes, did not manage to do it.
I tried VBAs copied from youtube videos and online forums, was not successful
I tried VBAs proposed in similar questions here on Stack Overflow, there were either mistakes with debugging or implementing it. Anything starting with "Sub" seems to be rejected by Apps Script in Google Spreadsheet. If it starts with "function" it debugs successfully, but i cannot implement it.

CodePudding user response：

function myFunction () {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var SourceSheet = ss.getActiveSheet(); 
var TargetSheet = ss.getSheetByName("Красноярск");
var LastRow = TargetSheet.getRange(2,3,10,1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() 1;
var SourceCell = SourceSheet.getActiveCell();
var SourceRow = SourceCell.getRow();
var SourceRangeF = SourceSheet.getRange(SourceRow, 2,1,3);
var SourceRangeS = SourceSheet.getRange(SourceRow, 7,1,6);
SourceRangeF.copyTo(TargetSheet.getRange(LastRow,2,1,3));
SourceRangeS.copyTo(TargetSheet.getRange(LastRow,7,1,6));
}

CodePudding user response：

Based on your code, if the two sheets have same structure, You need to get the last row not all rows as you did in your code, then just append the copied data by using appendrow() method the following:

function lastrow2() {
var source = SpreadsheetApp.openById('MY ID');
var sourcesheet = source.getSheetByName('sheet1');
var target = SpreadsheetApp.openById('MY ID')
var targetsheet = target.getSheetByName('sheet2');
var rangeValues = sourcesheet.getRange(sourcesheet.getLastRow(),1,1, 
sourcesheet.getLastColumn()).getValues(); //these are the values of last row
targetsheet.appendRow(rangeValues);

}