XL 2019 Macro Inserer Ligne

[Naniomasu]

Bonjour a tous,
je souhaite créer une Macro qui insert une ligne a la fin de plusieurs tableau en même temps et en respectant la mise en forme et l'application des formules de chaqu'un d'eux. J'ai utiliser l'enregistreur de macro qui me donne le résultat suivant :

function AjouteruneLigne() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A77:K77').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A77:K78'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('H78').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('F78').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Controle'), true);
spreadsheet.getRange('A77:G77').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A77:G78'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('G78').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Mai 2019'), true);
spreadsheet.getRange('B78:BX78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('B78:BX79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('AN79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('AC79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('L79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('K79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('O79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('AE79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('AU79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('C79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Controle'), true);
spreadsheet.getRange('A78').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('D78').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Symthese'), true);
spreadsheet.getRange('A78').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('I78').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('J78').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('D78').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Controle'), true);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Mai 2019'), true);
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Juin 2019'), true);
spreadsheet.getRange('A78:BO78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BO79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Juillet 2019'), true);
spreadsheet.getRange('A78:BS78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BS79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Aout 2019'), true);
spreadsheet.getRange('A78:BZ78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BZ79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('C79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Septembre 2019'), true);
spreadsheet.getRange('A78:BO78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BO79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Octobre 2019'), true);
spreadsheet.getRange('A78:BU78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BU79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('C79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Novembre 2019'), true);
spreadsheet.getRange('A78:BZ78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BZ79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Décembre 2019'), true);
spreadsheet.getRange('A78:BQ78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BQ79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Janvier 2020'), true);
spreadsheet.getRange('A78:BW78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BW79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Février 2020'), true);
spreadsheet.getRange('A78:BZ78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BZ79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('BX79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Mars 2020'), true);
spreadsheet.getRange('A78:BQ78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BQ79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Avril 2020'), true);
spreadsheet.getRange('A78:BY78').activate();
spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('A78:BY79'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
spreadsheet.getRange('BW79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('A79').activate();
spreadsheet.getCurrentCell().setValue('');
spreadsheet.getRange('B79').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Symthese'), true);
spreadsheet.getRange('F78').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Controle'), true);
spreadsheet.getRange('H78').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Symthese'), true);
};
Ce code ne fonctionne pas, il ne crée pas de nouvelle ligne mais reste sur les ligne que j'ai creer lor de l'enregistrement de la macro.
ou est mon erreur ?

je vous remercie d'avence,

Cordialement,

 

[Staple1600]

Bonjour le fil,

Naniomasu
Ce n'est point du VBA d'Excel cette affaire?
Goo.le Sheets?
Dans ce cas, tu n'es pas dans la bonne section du forum.
Ici on ne cause qu'Excel

La bonne section c'est ici

Autres applications

Pour vos questions concernant Power BI, Access, Word, Power point, Outlook, Microsoft project,... et les tableurs alternatifs tels que Google Sheets et LibreOffice Calc

www.excel-downloads.com