Copy to non consecutive lines

Discuss the spreadsheet application
Post Reply
campanico
Posts: 50
Joined: Wed Nov 06, 2013 4:48 pm
Location: X: viagrasansordonnancefr.com/

Copy to non consecutive lines

Post by campanico »

Hi all

Is it possible to copy values from sheet1 to sheet2 non consecutive lines?

Sheet1: Copy To Sheet2

A1 B1 C1 D1 ======> A12 B12 C12 D12
A2 B2 C2 D2 ======> A25 B25 C25 D25
A3 B3 C3 D3 ======> A37 B37 C37 D37
A4 B4 C4 D4 ======> A50 B50 C50 D50...

TYVM in advance
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy to non consecutive lines

Post by Zizi64 »

If you must to do it often, then the best solution is the Macro.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Copy to non consecutive lines

Post by Villeroy »

1) Copy A1:D999 to Sheet2.A12
2) E12: =HYPERLINK(CONCATENATE("#";ADDRESS(ROW()+1;1);":";ADDRESS(ROW()+12;5)))
3) Copy down E12 (double-click cell handle)
4) Click the first hyperlink E12
5) menu:Insert>Cells [Shift cells down]
---
6) Click next hyperlink
7) menu:Edit>Last command [Ctrl+Shift+Y]

repeat 6 and 7
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy to non consecutive lines

Post by Lupp »

@campanico:
Would you mind to explicitly tell if your steps actually are 12, 13, 12, 13 ... and how this sequence should be continued?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
campanico
Posts: 50
Joined: Wed Nov 06, 2013 4:48 pm
Location: X: viagrasansordonnancefr.com/

Re: Copy to non consecutive lines

Post by campanico »

@Lupp the steps in reality are 32,33,32,33,32,32 till around line1800

thks
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Copy to non consecutive lines

Post by Zizi64 »

32,33,32,33,32,32
Are the steps always alternated 32 / 33 / 32 / 33 /32 / 33 / ... ; or the 32 is really repeated somewhere or somehow?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy to non consecutive lines

Post by Lupp »

It should be obvious that there cannot be a "general" solution for sputtering rows as long as there isn't an absolutely unambiguous statement about the row numbers to use or where to start and which numbers of rows to skip in every case.

Please finally give a clear, unambiguous, and definite explanation.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
campanico
Posts: 50
Joined: Wed Nov 06, 2013 4:48 pm
Location: X: viagrasansordonnancefr.com/

Re: Copy to non consecutive lines

Post by campanico »

I have uploaded a file of the real file.
The first sheet has names, style, sex, group etc.
The second sheet has the elements that i need to insert with orange background and those are the ones that change please help if you can, or maybe that is not possible to be done and i will have to change it one by one has i did for the first ten.

TYVM
Attachments
exemplo.ods
(90.43 KiB) Downloaded 98 times
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy to non consecutive lines

Post by Lupp »

I cannot believe that you actually need all the 'ScoreCards' at once being present at the same time in a sheet. I would suppose the 'ScoreCard'-sheet is for printing only. In this case a group of, say, 4 cards fitting in one sheet and controlling its references to data from other sheets based on a single offset-number would be a good idea. A little macro could then go through all the offset numbers (by steps of 2 e.g. considering the pairing) and serialize the printing.

Beyond my understanding in every case is a design alternating a certain period being 32 for a sequence, and then changing between 32 and 33. That cannot be reasonable, can it?

For a solution changing the original as little as possible see the attached file. The mentioned period was normalized to 32.
Attachments
aoo93543_exemplo_1.ods
(67.6 KiB) Downloaded 93 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
campanico
Posts: 50
Joined: Wed Nov 06, 2013 4:48 pm
Location: X: viagrasansordonnancefr.com/

Re: Copy to non consecutive lines

Post by campanico »

You are absolutely right I only need it for printing pourposes.

The ideal could be something like mail merge for writer
One A4 sheet with two or 4 Archers would do the trick
But i also need the increase of the starting number and the next ones to be printed
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Copy to non consecutive lines

Post by Lupp »

See new attachment.
The long sheet of ScoreCards is now completely obsolete for printing. The new sheet takes settings for the print which are applied by a Sub called if the button is clicked.

Of course, you can only call the Sub if "macro" execution was permitted when loading the file. You are prompted for the permission only if the macro security is set to 'Medium'.

Be careful with large ranges for printing. You may waste paper since the routine is rather raw and the 'Wait' property of the print-job ist set to 'False'. For additional information see viewtopic.php?f=21&t=56713 e.g.

No guarantee of any kind!
Attachments
aoo93543_exemplo_2.ods
(80.22 KiB) Downloaded 76 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply