I have a simple booking spreadsheet/calendar and each year I copy the last sheet to create a new sheet. The days of the week are in column A, and the weeks are separated by a line under each Sunday row. To move the dates to the right day of the week, each date needs to be moved down one line (leap years aside). This should be a simple cut and paste (or drag), except that the cell boundaries move as well. Can I shift the data down one line without moving the boundaries?
Ubuntu 24.04 LO 25.8.4.2
Write 364 in a cell, copy this cell, select your dates (in column A), and paste special with [x] Add (section Operations).
Thank you, Leroy. Thatâs clever. I didnât know about paste + add, but I see how that works. At present my âdatesâ are only formatted as a number 1-31 (or 28 or 30). If I formatted as dates and I used your method, Iâd still need to adjust the start and end of each month. There may be a simple solution using dates,but I thought that shifting the column down would be simple too. I was surprised that the boundaries shifted, and I thought there would at least be a paste+special to avoid that.
Thanks for your reply, @PKG. I should have attached the spreadsheet initially, and I have now done so. I guess that the days could be a date, but itâs not the way I set it up. The months are in separate columns, with a blank column after each to fill in the yearâs data. It would not be simple to link the days to dates when the actual dates are in multiple columns.
Ola @Pansmanser, segue sugestão para duplicação de planilha para outro ano.
InstruçÔes: Para novo ano, clique direito em uma aba da planilha, selecione Duplicar, altere o nome da planilha, para o Ano desejado, selecione a célula A1 e tecle F9.
Obs.: Coluna A esta oculta, e as linha 1 de cada mĂȘs, estĂŁo formatadas ocultas ( ;;; )
Hi @Pansmanser, hereâs a suggestion for duplicating a spreadsheet for another year.
Instructions: For the new year, right-click on a spreadsheet tab, select Duplicate, change the spreadsheet name to the desired year, select cell A1 and press F9.
TestBooks_GS.ods (31,8,KB)
Note: Column A is hidden, and row 1 of each month is formatted as hidden ( ;;; )
This is brilliant! Thanks @schiavinatto . In my system, I found it necessary to press Ctrl-Shift-F9.
However, Iâd still like to be able to copy and paste cell data without affecting the boundaries.
Explique melhor, se possĂvel com um exemplo deste procedimento.
Please explain further, if possible with an example of this procedure.
(I didnât check your macros.)
Your concept of ConditionalFormatting is wrong.
The boundaries below sundays are applied for the view by CF, but not assigned to the cells this wayâŠ
You assigned, however, the style âSundayâ additionally to many cells by explicit action via the UI.
Such assignments canât be changed by CF.
The given sheets need a basicall redesign.
First of all a âsynchronisationâ of column A with the other columns. is needed.
Insofar: Usage of literals for weekdays (or any date components) is a very bad idea.
If interested in my recommendations you may study the attached example.
disask_130745_WeeklySeparators_Bookkeeping.ods (105.9 KB)
This is embarrassing! Itâs so long since I first created this spreadsheet that I had forgotten there were macros! Iâll need to take another look at it. Thanks anyway.
If you used the sheets, but were not asked for permission to run the contained macros, your macro security must be set to Low, or the file saved to a trusted directory. I think you should check your settings of Macro Security options.
1 Like
High actually, with trusted sources.