LibreOffice Calc: How to transfer Column from Sheet 1 to Sheet 2 while adding empty Row between each Row of Sheet 1?

We have a table consisting of arbitrary dates in column A (sheet 1). Those dates will define measurement days when coworkers pick up samples to analyze them. The identified measurement values themselves will be written down in sheet 2, sheet 3, sheet 4 etc., but those sheets should always pick up the dates defined in sheet 1 automatically.

Each sheet contains several measurement value cells, this is why there needs to be some additional empty row below the value cell to add comments (in sheet 2, sheet 3, 4, etc.).

Assumed we have this kind of table in sheet 1:

1  2021-01-01
2  2021-02-15
3  2021-03-23

In sheet 2 I want to copy the date column from sheet 1, but add an empty row between each subsequent date row, so the coworkers can add notes below values X, Y and Z:

1  2021-01-01    X-Value             U-Value
2                (comment to X)     (comment to U)
3  2021-02-15    Y-Value             V-Value
4                (comment to Y)     (comment to V)
5  2021-03-23    Z-Value             W-Value
6                (comment to Z)     (comment to W)

As soon as one adds a new date row in sheet 1, in sheet 2 etc. this row should appear with a blank row distance above.

Is there any possibility to do so?

Would you be so kind (and clever) to explain the purpose, please. Good questions do,

@Lupp: Thank you very much for your hint! I have added an explanation - I hope it will be understandable. :slight_smile:

Thanks for your readiness to explain.
Unfortunately the additional information doesn’t help me to understand your interjectional rows as a reasonable design.
As it seems you want to put a kind of headers there, but “headers” only addressing one row of data are just another bad idea in addition to the extra rows.
Generally sptreadsheets should be used for some kind of evaluation, and any evaluation I can think of will seriously suffer from problems caused by that design.
How many kinds of “X-and-whatever-Value” do you think can occur (all dates included)?
If there is no clear namespace for them, I would doubt if your sheets can ever be more than a digitalized scratch-paper. If there is one you may need a step of database-thinking, and create an extra sheet identifying them and allowing to refer to them [by IDs which can serve in the other sheets as foreign keys, or] at least as a validation base for a sequence of horizontal pairs of (Valuetype, Value).

For your simple table, there is an easy way to do this. Insert the formula =CHAR(10) into column B and stretch it to the end of the data. Now copy all data with function results, go to a new sheet and paste the contents of the clipboard as a CSV

PasteAsCSV.png

Thanks for your help, but unfortunately this does not work as desired… :slight_smile: I need to add empty rows automatically as soon as new text was inserted into sheet 1. Your function does only add a new empty line into the cells of sheet 1, but without adding “real” rows into the spreadsheet.

Oh, excuse me, please - I misunderstood your wish " I want to copy those rows from sheet 1"… Do you want to do it automatically every time? And these blank lines - will they always remain blank? Or will something be recorded in them? Because a macro that is triggered every time the content on sheet 1 is changed can completely re-create the content on sheet 2 (if something appears in empty lines, it will be erased) In other words, @Lupp is right - the question must be more detailed, otherwise all the posted answers will be wrong.

@JohnSUN: Thank you very much! :slight_smile: Thanks to your request, I have updated my explanation. I hope you can understand better now. :slight_smile:

@Pantona Yes, now I understand better, I answered the wrong question. :slight_smile: I know you will believe me not today or tomorrow - months will pass before you admit that I was right. But nevertheless, I will write this now - “You have chosen the wrong tool for solving your problem, you do not need Calc, but the Base”. Just try to answer the question - what happens if two or more measurements are taken in one day?

Hi, @Pantona, test file

Macro linked to Spreadsheet Content Changed Events

REM  *****  BASIC  *****

Sub AlteredContentArea ( oCelula )
'------------------------------------------------------------------------------
'Por Grafeno: 'https://ask.libreoffice.org/t/como-executar-uma-macro-no-calc-ao-selecionar-uma-celula-de-uma-area/38209
' Testar se o objeto selecionado é uma célula individual '
If oCelula.ImplementationName <> "ScCellObj" Then Exit Sub
' Endereço da Célula '
oEnd = oCelula.CellAddress
' Celula no intervalo B7:B27 '
' Coluna 1 e linha entre 6 e 26 '
If oEnd.Column = 0 Then
If oEnd.Row >= 1 And oEnd.Row <= 50 Then
' Chamar a macro desejada '
Call Pantona
' -----------------------
End If
End If
End Sub


sub Pantona
'-------------------------------------------------------------------------
dim document   as object
dim dispatcher as object
'-------------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$Plan2.$A$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, Array())
'-------------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "StringName"
args3(0).Value = " "
dispatcher.executeDispatch(document, ".uno:EnterString", "", 0, args3())
'-------------------------------------------------------------------------
dim args8(0) as new com.sun.star.beans.PropertyValue
args8(0).Name = "ToPoint"
args8(0).Value = "$Plan1.$A$1048576"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args8())
dispatcher.executeDispatch(document, ".uno:GoUpToStartOfData", "", 0, Array())
'-------------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SetInputMode", "", 0, Array())
dispatcher.executeDispatch(document, ".uno:Cancel", "", 0, Array())
End Sub 

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.