Macro to Copy Current Sheet and insert it after current sheet

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 close merge delete

Sort by » oldest newest most voted

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.

more

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.

( 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

( 2019-03-20 13:01:27 +0200 )edit