It may not be possible. I tried to “Record Macro” . When a 14 row game (on page one) is deleted, I need to move all the games up. There are 6 games per page, 7 pages, or… 42 games in all. 1) “Insert Rows below” (14) on the bottom of first page. 2) Copy the first game on the second page (14 rows) and Paste it into the 14 rows I inserted above. 3) Delete the first 14 rows on the top of the second page. etc… When I was finished with 7 pages and stopped recording - the rows were out of order. When I perform this tedious operation by hand, it works just fine. Am I missing something when “inserting and Deleting” rows within MACRO?
Hint: while recording a macro, put the mouse aside, perform all operations of selecting rows and sheets only from the keyboard - most often, incorrect execution of a macro is due to the fact that cell addresses obtained with the mouse are not recorded correctly.
Recording a macro like this may be possible, but it is an anti-pattern. If the games ever use different numbers of rows each, you might create a macro using the UNO API, or ask for more details about how to do that here. But if the games each always have the same number of 14 rows then:
- Put all of your games on a single sheet. Name it’s tab Data.
- Build your 7 sheets called Page1, Page2, etc., using references to the Data sheet. If you are convinced 14 is fixed, then you can just build the sheets directly. Otherwise you could use INDIRECT() and a little modular mathematics.
For the direct version, just put =Data.A1 in the first cell of Page1. =Data.B1 in the next right cell, etc. Then select the finished first row and pull down to row 14*6=84. On Page2 start with Data.A85, complete the row, then pull down to row 84. Etc.
Now you can edit/insert/delete games in Data all you want, but the pages will still work out. Just don’t add literal data to the PageN sheets, only use them for copying from Data or for calculated columns, since calculated columns will automatically re-evaluate as needed.
Choose it from menu Sheet-Insert rows-Insert below (Alt+S, R, B)
It insert to macro line
dispatcher.executeDispatch(document, ".uno:InsertRowsAfter", "", 0, Array())
Very nice! Alt+S,R,B works well. NOW, I’m using a wireless Keyboard with a numerical pad. CTrl (-) doesn’t always delete the selected 14 rows. Got another shortcut for that? 