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 <[email protected]> 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 > macro. > > 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) > Next > > 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 >> document, >> 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 >> it, >> 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 >> >> Cheers >> Phil >> >> >> On 8 June 2014 22:00, Tom Davies <[email protected]> wrote: >> >> Hi :) >>> Andrew Pitonyak's guide is pretty good and might be helpful >>> >>> https://wiki.documentfoundation.org/Documentation/Other_ >>> Documentation_and_Resources#Programmers >>> >>> Really it is best to just ask the question here and let this team >>> build-up >>> the best answer by trying out different things. >>> Good luck and regards fro m >>> Tom :) >>> >>> >>> >>> >>> On 8 June 2014 19:23, Philip Ward <[email protected]> wrote: >>> >>> Hi Guys, >>>> >>>> Who is the best person to speak to about macro's in calc? >>>> >>>> cheers >>>> Phil >>>> >>>> -- >>>> To unsubscribe e-mail to: [email protected] >>>> Problems? >>>> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ >>>> Posting guidelines + more: http://wiki.documentfoundation.org/ >>>> Netiquette >>>> List archive: http://listarchives.libreoffice.org/global/users/ >>>> All messages sent to this list will be publicly archived and cannot be >>>> deleted >>>> >>>> >>> > -- > Andrew Pitonyak > My Macro Document: http://www.pitonyak.org/AndrewMacro.odt > Info: http://www.pitonyak.org/oo.php > > > -- > To unsubscribe e-mail to: [email protected] > Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to- > unsubscribe/ > Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette > List archive: http://listarchives.libreoffice.org/global/users/ > All messages sent to this list will be publicly archived and cannot be > deleted > -- To unsubscribe e-mail to: [email protected] Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
