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

Creating a macro - Writing a Script - Using the API

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

Postby blokenone » Tue Feb 23, 2021 11:51 pm

Hi,

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

Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
oSheets = ThisComponent.Sheets
oSheets.copyByName("Glad_template", "TemplateCopy", oSheets.Count)
OpenOffice 4.1.9 Windows 10
blokenone
 
Posts: 2
Joined: Tue Feb 23, 2021 11:45 pm

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

Postby FJCC » Wed Feb 24, 2021 12:19 am

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   Expand viewCollapse view
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getByName("Sheet1")
Name = oSheet.getCellrangeByName("NameCell").String
oSheets.copyByName("Sheet1", Name, oSheets.Count)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8104
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby blokenone » Wed Feb 24, 2021 12:27 am

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
blokenone
 
Posts: 2
Joined: Tue Feb 23, 2021 11:45 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 4 guests