Calc: Copy range of cells from all sheets into new sheet

[mariachi]

I have 20 identical sheets in the same file and I want to combine them into a single sheet.
Additionally, I would like to create a new column in the “master” sheet with the name of copied sheet, sort of like a label.

Here is an excerpt of my data:
https://ufile.io/7j74g

Each set of data starts with an identifier “TX1” or “TX2”. It would be nice if a solution included a second column in the “master” sheet with this, but it’s not strictly necessary, I can do it by hand later as long as the two ranges are in the correct order.

name_of_sheet   TX1   data
name_of_sheet   TX1   data
name_of_sheet   TX1   data

[JohnSUN]

Thanks for the interesting question - it was not a trivial task. Try the macro from this file Copy range of cells.ods

[mariachi]

Thank you! I’ve never used macros in Calc before though… How do I actually run this? I double click it and nothing happens (I already copied my sheets into your file)

[JohnSUN]

Do you mean that you press Alt+F11, select macro collectData and push button Run? And nothing happens?.. Not created new spreadsheet with all your data? Please verify that option Tools - Options - LibreOffice - Security - Macro Security set to Medium

[mariachi]

That was it! I can’t find a way to save this macro to “my macros” or something like that so I can use it in other sheets. I googled and most answers are about recording and then saving. Thank you very much, you’ve saved me a couple hours of copy-pasta.

[JohnSUN]

A way to save this macro to “my macros” very simple - Alt+F11, than push Organizer and in tab Modules just drag-and-drop module CopyData2SnglSheet to My Macros - Standard