Ask Your Question

Copy by preview SHEET

asked 2016-01-15 17:07:13 +0100

STer gravatar image

I have a document that contains 31 sheet (sheets name is 1,2,3, ... for each day of the month) I created a macro at startup to select the name day (if today is Jan 10. Sheet Named selected is 10)

  ThisComponent.CurrentController.ActiveSheet = ThisComponent.Sheets.getByName( format(Now(), "d"))

Now... I want to add value A1 of the previous sheet in the current sheet A1, but higher by 1

Example: in cell A1 of the current sheet, I want to be added automatically the value of the previous sheet A1, but higher by 1

'9'.A1 = 100

'10.A1 = 101

How can make this ?

thank you in advance

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-01-19 01:05:23 +0100

BasicMacro gravatar image

This works even if the previous day was the last day of a month (e.g 30 or 31).

Dim iValue As Integer
' Get value from previous sheet'
iValue = ThisComponent.Sheets.getByName(Day(Now()-1)).getCellByPosition(0, 0).getValue()
ThisComponent.CurrentController.ActiveSheet = ThisComponent.Sheets.getByName(Day(Now()))
' Set value in todays sheet'
ThisComponent.Sheets.getByName(Day(Now())).getCellByPosition(0, 0).setValue(iValue+1)

.getCellByPosition(nColumn, nRow)
    nColumn is the column index of the cell.
    nRow    is the row index of the cell.

Cell positions start at zero.
A1 = (0,0)
B1 = (1,0)
A2 = (0,1)
A5 = (0,4)
E8 = (4,7)

Other methods

  • .setValue(Number)
  • .setString(Text)
  • .setFormula(Formula)
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-01-15 17:01:04 +0100

Seen: 26 times

Last updated: Jan 15 '16