[Calc] Copy a sheet and rename based off named range

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
blokenone
Posts: 2
Joined: Tue Feb 23, 2021 11:45 pm

[Calc] Copy a sheet and rename based off named range

Post by blokenone »

Hi,

I have this macro in Excel and need an OpenOffice version. I have put the full macro from excel below:

Code: Select all

Sub Make_Glad_Sheets()
   Application.ScreenUpdating = False
    Sheets("DUMP").Activate
    Dim xRg As Excel.Range
    Dim wSh As Excel.Worksheet
    Dim wBk As Excel.Workbook
    Set xRg = Sheets("DUMP").Range("Glad_list")
    
     Dim xWs As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each xWs In Application.ActiveWorkbook.Worksheets
        If xWs.Name <> "Home Sheet" And xWs.Name <> "DUMP" And xWs.Name <> "Reference" And xWs.Name <> "Glad_template" Then
            xWs.Delete
        End If
    Next
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
        
    For Each xRg In Sheets("DUMP").Range("Glad_list")
        On Error Resume Next
        Sheets("DUMP").Activate
        Application.DisplayAlerts = Flase
        ThisWorkbook.Sheets(xRg.Value).Delete
        Application.DisplayAlerts = True
    Next xRg

    For Each xRg In Sheets("DUMP").Range("Glad_list")
        Sheets("DUMP").Activate
            
        With wBk
            Sheets("Glad_template").Copy After:=Sheets(Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = xRg.Value
            If Err.Number = 1004 Then
                Debug.Print xRg.Value & " already used as a sheet name"
            End If
            On Error GoTo 0
        End With
    Next xRg
    
    Application.Calculation = xlAutomatic
    Application.StatusBar = False   ' Clear the status bar in case it isn't blank for any reason
    Sheets("Home Sheet").Activate   ' Go back to the Home Sheet sheet
    Application.ScreenUpdating = True
End Sub
Teaching myself this in Excel took me a few days and my head isnt coping with OpenOffice Macroing at the moment, I managed to get the Macro to copy the sheet, but its the rename based off range which is killing my head:

Code: Select all

oSheets = ThisComponent.Sheets
oSheets.copyByName("Glad_template", "TemplateCopy", oSheets.Count)
OpenOffice 4.1.9 Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro - Copy a sheet and rename based off named range

Post by FJCC »

If you have a cell named NameCell on Sheet1, you can use its text to name a copy of Sheet1 like this.

Code: Select all

oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByName("Sheet1")
Name = oSheet.getCellrangeByName("NameCell").String
oSheets.copyByName("Sheet1", Name, oSheets.Count)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
blokenone
Posts: 2
Joined: Tue Feb 23, 2021 11:45 pm

Re: Macro - Copy a sheet and rename based off named range

Post by blokenone »

Thank you for the reply - I have a tab with a CSV export pasted in and in column A of that sheet I have the list of sheet names I want and then there is other data used by VLOOKUP in a template sheet (which is the one copied and renamed)

There is a named range which exists so I am hoping to have a For loop like I coded in Excel so it copies the template, renames the new sheet by the value in A2 in the CSV Dump page, then moves on to A3 and repeats until the loop is completed for each value in Column A
OpenOffice 4.1.9 Windows 10
Post Reply