Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello,

Based upon another of your questions, you have little to no macro experience. Please note that you should not be using this site for free programming services. It is common to present some type of code you already have and ask for help concerning a problem.

An excellent source is Open Office Macros Explained by Andrew Pitonyak. This is best all-in-one reference; PDF found here -> OOME.

Without knowledge and continued increasing use of macros written exclusively by others, you will certainly have problems in the future.

Part of the information you need is in Chapter 15. Calc Documents. In the first few pages it covers copying sheets.

Now one of the items in coding a macro is covering processing which you fail to do in your question. When you copy the sheet, as in your request, you want it inserted after the current sheet. But what is the sheet to be Named? What if you specify a name but the sheet name exists already? These and possibly more questions arise when dealing with macros.

With that stated, here is a macro which you can attach to a button (execute action event), or create a toolbar item and attach to that:

Sub CopySheet
    Dim oCurrentController As Object
    Dim oActiveSheet As Object
    Dim oSheets As Object
    oCurrentController = ThisComponent.getCurrentController()
    oActiveSheet = oCurrentController.getActiveSheet()
    oSheets = ThisComponent.getSheets()
    If oSheets.hasByName( oActiveSheet.Name & "Copy" ) Then
        MsgBox "Sheet name already exists"
    Else
        oSheets.copyByName(oActiveSheet.Name, oActiveSheet.Name & "Copy", oActiveSheet.RangeAddress.Sheet + 1)
    End If
End Sub

The new sheet will be named using the original name & "Copy". If that already exists, a message box will appear stating the name already exists.

Hello,

Based upon another of your questions, you have little to no macro experience. Please note that you should not be using this site for free programming services. It is common to present some type of code you already have and ask for help concerning a problem.

An excellent source is Open Office Macros Explained by Andrew Pitonyak. This is best all-in-one reference; PDF found here -> OOME.

Without knowledge and continued increasing use of macros written exclusively by others, you will certainly have problems in the future.

Part of the information you need is in Chapter 15. Calc Documents. In the first few pages it covers copying sheets.

Now one of the items in coding a macro is covering processing which you fail to do in your question. When you copy the sheet, as in your request, you want it inserted after the current sheet. But what is the sheet to be Named? What if you specify a name but the sheet name exists already? These and possibly more questions arise when dealing with macros.

With that stated, here is a macro which you can attach to a button (execute action event), or create a toolbar item and attach to that:

Sub CopySheet
    Dim oCurrentController As Object
    Dim oActiveSheet As Object
    Dim oSheets As Object
    oCurrentController = ThisComponent.getCurrentController()
    oActiveSheet = oCurrentController.getActiveSheet()
    oSheets = ThisComponent.getSheets()
    If oSheets.hasByName( oActiveSheet.Name & "Copy" ) Then
        MsgBox "Sheet name " & oActiveSheet.Name & "Copy, already exists"
    Else
        oSheets.copyByName(oActiveSheet.Name, oActiveSheet.Name & "Copy", oActiveSheet.RangeAddress.Sheet + 1)
    End If
End Sub

The new sheet will be named using the original name & "Copy". If that already exists, a message box will appear stating the name already exists.