creating a macro to copy and paste a range of cells into the next available blank cell

Question

i am so very new to macros - quite the wiz at excel if i do say so myself hahaha but have never done macros and script writing before, basically i have 2 sheets, one is to schedule jobs and the other is to tell manufacturing which of those jobs are needing to be manufactured, the cutting sheet automatically populates using a filter formula based on specific cells in the scheduling sheet.

what i wanted to do was create a button whereby we can just click it and a new week will appear in the scheduling tab and then one in the cutting sheet tab, but when i record the macro it just copies and pastes into the same cells, i dont know how to get it to copy and paste underneath the previous week, i have attached the sheet.. i hope i did it right

can someone please let me know how to record the macro so it copies and pastes the rows under the previous week :)

https://docs.google.com/spreadsheets/d/1iBs0-HJOowa-GrQp-0avi4oVy9XZzzbYK669D9KJlT8/edit?usp=sharing

What is the objective with repeatedly adding weeks (at the bottom?) of the scheduling and cutting sheets? Do you expect to have a year's worth of data in this sheet? With the many formulas you have per week, performance might become an issue as the sheet grows. Is it possible that doing one week per tab, or possibly one month per tab, might be more efficient? Duplicating tabs to add another week or month would be simple. And with a good naming convention, the data into or from all tabs could be easily automated, given an identical structure. — kirkg13
I also believe that a lot of your formulas could be consolidated, using the strength of ArrayFormulas in GoogleSheets, which might help performance, as well as maintainability. But I'll let some of the experts here speak up on that if they also recommend that. — kirkg13
hi, thanks for your comment, we need to have the cutting sheet all on the same tab and always 6 months of data at a time, this is to see if ever we have any duplicate jobs that get manufactured. i found that that can only happen if the cutting sheet is all on the same tab which in order for the filter to accurately work the information it is filtering needs to be on the same tab... does that sound right to you? i wish i had the patience to add array formulas but this has just already taken soooo long hahaha i dont know that i have the energy in me!! — Stegbar Logistics
Well, I'd love to see a copy of your sheet filled in with a lot of sample data. I'm quite sure you could have a summary sheet that could easily find any duplicates across all of your tabs, especially since they would all have the same structure. But I understand a relucatance to change, when you've invested a lot of time. I just have a feeling that you may run into issues with this design as you add six months of data to this sheet. Good luck. — kirkg13
honestly - i fyou know a better way of doing it i would love to hear it, this took alot of googling to find different formulas, alot of trial and error to try and work out the best way to do it... this is all i could come up with, your obvioulsy much more advanced at google sheets than i am so if you have any suggestions i would love to hear them... i have no doubt that once we actually start using it within the business i will find multiple things that require changing/fixing. — Stegbar Logistics

kirkg13 kirkg13 · Accepted Answer · 2020-11-19T05:48:15

This is very basic, but I found that I was able to record a macro that seems to do what you want. I've just done it for the Cutting Sheet, but the same logic should work for the schedule sheet.

Macro/script is as follows:

function AddWeekCuttingSheet() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Cutting Sheet'), true);
  spreadsheet.getRange('A2').activate();
  spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
  spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);
  spreadsheet.getActiveRange().offset(spreadsheet.getActiveRange().getNumRows(), 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('2:84').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

If you use Tools > Script editor, and paste in the above script, rename it if desired, then save - it should be available to run as a macro from your meu bar. It may require authorisation the first time. Make a copy of your sheet to test it on first. To simplify your testing, in Cutting Sheet, you could delete rows 85 and higher (Week 2 and up), but this is not necessary.

The macro goes to sheet "Cutting Sheet", goes to column A, and goes to the last data cell, adds a blank row, then pastes the contents of row 2 to row 84 at that location.

I'm sure that there are better solutions, ones that others may propose, but this may help you in some way.

creating a macro to copy and paste a range of cells into the next available blank cell

1 Answers