Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index

Hi :)
Ahh, i hadn't tried this either and this makes much more sense to me.

So it looks like there are many ways of dodging the need for a macro and
that helps keep the document flexible for the future.
Regards from
Tom :)

On 10 June 2014 06:08, Andrew Douglas Pitonyak <> wrote:

Be certain to only do what you really need to do using a macro, and then
let things such as formulas do the rest of the work.

It sounds like you want to have a Calc document with 366 sheets (so that
it will work with leap years) with the same sheet on each page. Well, the
sheet is the same except for the date.

I might argue that you could simply leave the date empty and let that be
filled in, but, lets ignore that for a second.

As a test, I created a Calc document. On Sheet 1, I entered the text
"Date" into A1. I entered the date "01/01/14" in cell B1.
I created Sheet2 and in cell A1, I entered the formula: "=Sheet1.A1". In
cell B1, I entered the formula: "=Sheet1.B1 + 1".
I then copied cells A1:B1, created a new sheet (Sheet3), and then I pasted
those two cells into location A1:B1 on sheet3.

At this point, Cell A1 has the same text on each sheet, and, cell B1 has
the dates 1/1/14, 1/2/14, and 1/3/14. Now, if I updated cell Sheet1.B1 with
a new date, then B1 updates in all the other sheets.

Is there any particular reason that you must use a new sheet for each
printed page? Might you be able to create all the pages on the first sheet
such that they are all on the first sheet? You would place the second page
far enough down that it would then print on the second page and use
formulas to refer to the previous "page / date", and then do a copy and
paste of multiple pages in one shot. Admittedly, this will use a bunch of
rows (around 18000 is my guess), but that is likely faster than created a

If you do opt to write a macro....

Listing 403 in OOME_3_0.odt demonstrates inserting sheets. The code would
probably look something like:

Dim i As Integer
For i = 1 to 365
  ThisComponent.Sheets.insertNewByName("Sheet" & CStr(i + 1), i)

OK, now you have 366 sheets. You probably want to copy the content from
sheet (i-1) to sheet i as you go. If you raelly want to transfer that
content, you can do something like get the transferable content (Listing
439 has an example), but, to do that, you would need to set the desired
sheets to be active, which just feels annoying and tricky (but it can be
done). Probably easier to:

Manually set Sheet 1 as you want.
Build Sheet 2 by hand using formulas to refer to Sheet 1. Write the macro
to insert the sheets after 2. If you only want to copy formulas, then use
getFormulaArray on sheet 2, then use setFormulaArray for the rest of the
sheets... And I need to go to sleep now.

On 06/09/2014 06:24 PM, Philip Ward wrote:

Hi Tom/all

sorry for delay in getting back, and cheers for the info, ill take a look
tomoz night and the link, that much on the go at the moment...

basically what im after is...

A cleaning/HSE Check sheet, that gets printed out, and the staff fill it
out, ie tick the boxes, say whats gone of in that day etc.

i have laid out a sheet, over 2 pages, and the only thing that i want to
basically do is print out 365 (or obviously a leap year 366) page
and automatically add a new date to the printed sheet for that.

i didnt want to copy and paste the same thing 365 times, for 4 or 5 times,
as there are different parts of the business, ie Cafe/Bar/Icecream kiosk
etc, which all need their individual style/setup.

i was looking for a quick way to copy the sheet, add a new sheet, paste
then do the same thing over and over again. On the second sheet i know i
can make a date = Previous sheet/Cell and +1 to it, and this would be the
one thats copied all the rest of the year, but thats about where my
knowledge on libre finishes (having brought most of what i knew from
excel), but have never done any macro's of this sorts before, well part
from gimp to re-size/touch up pictures, and even then found that i could
have done it quicker manually!

i just would prefer not to copy and paste something a potential 1825
times!! my control and V key probably woudlnt like me very much


On 8 June 2014 22:00, Tom Davies <> wrote:

 Hi :)
Andrew Pitonyak's guide is pretty good and might be helpful

Really it is best to just ask the question here and let this team
the best answer by trying out different things.
Good luck and regards fro m
Tom :)

On 8 June 2014 19:23, Philip Ward <> wrote:

 Hi Guys,

Who is the best person to speak to about macro's in calc?


To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be

Andrew Pitonyak
My Macro Document:

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.