Calc: Copy a portion of a sheet to another sheet?

Bort · August 13, 2021, 4:40pm

I want to copy only a specific range from one sheet to another sheet in the same document so that if data is changed on one sheet it also changes on the other. How do I copy/paste the range so everything is copied; formatting, images, formulas, conditional formatting, column and row sizes, form controls…everything…so they’re positioned the same where they’re pasted?

Copy/pasting the range doesn’t also carry the column and row width and height or position. It’s a mess when it pastes in the other sheet.
How do I paste it so it’s exactly the same?

Lupp · August 13, 2021, 7:41pm

Clarifications:

You should tell if the top-left target cell has the same address inside Its sheet as the top-left cell of the source range.
Copied Calc formulas may have relative parts in addresses. If you paste them they will adapt as is specified for copied formulas. This also applies to the sheet part if present.
Same with formulas used for ConditionalFormatting.
All shape-hosted objects (images, graphics, FormControl objects and OLE objects should anyway be posted as you want it.
ColumnWidth and RowHeight are only pasted if the respective columns or rows were selected as a whole each during the copa-step.

I don’t think you can get everything in one go.

If you want formulas pasted as literals (without any adaption) you will need to use rather complicated workarounds or not quite simple user code (developed “macros”).
If you actually need this for some strange reason, post a specialized question, and explain the context/use-case, best also attaching an example .ods.
Even more complicated.
Nothing to do. The way it’s done by default, imo.
No handy workaround as far as I can see, except you can accept a first copy/PasteSpecilaFormatsOnly for the range of complete columns/rows and a second copy/PasteSpecilaFormatsOnly for the range of complete rows/columns…Each such step may overwrits cell formats (including CF) outside the range you wanted to copy. If you need help with a workaround by user code, come back with a more detailed explanation and … (see above).

Bort · August 13, 2021, 9:12pm

I am getting everything in one copy. I chose the source sheet (the whole sheet with the top left corner selection header space/“button”), then did the same on the sheet I want the stuff to go to. I then pasted-special and pasted with only Formats selected. That transferred the row and column sizes, cell merges, etc of the entire sheet plus some cell background colors but not all of them.

When I copied cell A1:G20 which has a background image, a 2nd graphic, check boxes in column G from cell 4 to 20, and conditional formatting in cells C4:E20 and used just ‘paste’ it pasted everything and everything retained their position.

The only thing that didn’t copy was the conditional formatting. The only way I found so far to get conditional formatting to work on both sheets simultaneously is to reference the main_sheet in the condition on the other sheets.

The ranges aren’t always going to be in the same cells on every sheet. I have to manually change all the formula’s, conditional formatting, etc, to reference the main sheet it comes from?
Like there isn’t a way to select a cell and put a formula in it that posts everything from the specified sheet the formula tells it to get it from in all the cells in a specified range?

I know this probably isn’t a formula syntax it’s to example what I mean if it’s misunderstood:

=refrence(Sheet!Main_Sheet,$A1:G20,K10:P30)
In the fake example formula above it gets range A1:G20 from Main_Sheet and displays everything in those cells in cells K10:P30 on the sheet I put the formula in.

That’s not possible?