Ask Your Question

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

asked 2017-09-08 12:30:43 +0200

mariachi gravatar image

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:

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
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-09-08 14:21:50 +0200

JohnSUN gravatar image

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

Don't write "Thank you"

edit flag offensive delete link more


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)

mariachi gravatar imagemariachi ( 2017-09-08 14:40:37 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2017-09-08 15:06:05 +0200 )edit

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.

mariachi gravatar imagemariachi ( 2017-09-08 15:11:40 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2017-09-08 15:38:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-08 12:30:43 +0200

Seen: 545 times

Last updated: Sep 08 '17