Ask Your Question
0

Macro to Copy Current Sheet and insert it after current sheet

asked 2019-03-20 03:23:25 +0200

Boggle gravatar image

Then I'd like to create a button out of this. I want it to copy the current active sheet, then insert it after the current active sheet.

Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-03-20 06:01:24 +0200

Ratslinger gravatar image

updated 2019-03-20 06:06:16 +0200

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.

edit flag offensive delete link more

Comments

FYI - LO Calc documentation can be found here ->LibreOffice Calc Guide

Probably best to look in Writer or Base section for information regarding push button. Forms chapter & anything with macros may help.

Ratslinger gravatar imageRatslinger ( 2019-03-20 06:57:22 +0200 )edit

Sorry about that. Thanks for the resources, I'll definitely give them a read. I would like to be able to learn and apply macros on my own. Thanks also for answering my question. In the future, I'll try to figure it out first, and then ask here if I'm truly stuck, does that sound more in-line with this forum?

Yeah, I didn't present information about the sheet naming preferences because I figured that I could stumble my way through that part. What you've provided is helpful and probably all I need. I will save this macro and the other from my other comment, use them in parallel with the reading material you've provided, to better understand how to write and apply macros. Thanks again

Boggle gravatar imageBoggle ( 2019-03-20 13:01:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-20 03:23:25 +0200

Seen: 33 times

Last updated: Mar 20