I am creating a scorebook to facilitate keeping statistics for a 70 game season for 35 players. Sample page below. It is intended that page 36 will be a total page which will give me
season stats for each game on each line with season totals at the bottom of the page. The individual pages would show stats for each player.
My next step is to duplicate the page I have created 36 times but other than copying and inserting 36 times or writing a macro, I don't see an easy way to do this.
Someone has no doubt done something very similar to this and any advice would be greatly appreciated. I'm doing this primarily to enhance my modest Calc abilities
[Solved] Duplicating formatted worksheet several times
[Solved] Duplicating formatted worksheet several times
Last edited by sigsky on Tue Feb 20, 2018 1:55 am, edited 1 time in total.
Windows 10
Open Office 4.1.11
Don't want to buy Excel
Open Office 4.1.11
Don't want to buy Excel
- stickman68
- Posts: 4
- Joined: Mon Feb 19, 2018 1:13 am
- Location: NY, NY
Re: Duplicating formatted worksheet several times in a workb
I'm sure this is a terrible answer but here is a macro that takes a list of sheet names and copies a template sheet.
Code: Select all
Sub CreateSheets
Dim oWkb as Object
Dim oSheets as Object
Dim ogetSheet As Object
DIM CellRange as Object
DIM HLS as Object
DIM row as long
DIM my_cell as Object
DIM nameSheet as String
oWkb = ThisComponent
oSheets = oWkb.Sheets
ogetSheet = oWkb.getSheets()
origSheet = oWkb.CurrentController.ActiveSheet.getname
LS = oSheets.getByName("List Sheet")
oCursor = LS.createCursor
oCursor.gotoEndOfUsedArea(False)
LastRow = oCursor.RangeAddress.EndRow
LastRow = LastRow + 1
Lastsheet = "$A$" & LastRow
Firstsheet = "$A$2"
mysheets = Firstsheet & ":" & Lastsheet
CellRange = LS.getCellRangeByName(myHosts)
ThisComponent.LockControllers
row = LastRow - 1
While row > 0
col=0
my_cell = LS.getCellByPosition(col,row)
cell_value = my_cell.String
if ogetSheet.hasByName(cell_value) Then
MsgBox("This Sheet Name already exists: " & cell_value)
else
nameSheet = cell_value
ThisComponent.Sheets.copyByName("Template",nameSheet,thisComponent.Sheets.Count())
ThisComponent.Sheets.moveByName(nameSheet, 1)
End If
row = row - 1
Wend
ThisComponent.UnlockControllers
ThisComponent.CurrentController.Select(origSheet)
End Sub
LibreOffice 6.0.1 on Windows 10/ LibreOffice 6.0.1 with MacOS 10.13 / LibreOffice 6.0.1 on Linux Mint 18
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Duplicating formatted worksheet several times in a workb
Select the sheet. Edit → Sheet → Copy → OK. You now have 2.sigsky wrote:My next step is to duplicate the page I have created 36 times but other than copying and inserting 36 times or writing a macro, I don't see an easy way to do this.
Select the 2 sheets. Edit → Sheet → Copy → OK. You now have 4.
Select the 4 sheets. Edit → Sheet → Copy → OK. You now have 8.
Select the 8 sheets. Edit → Sheet → Copy → OK. You now have 16.
Select the 16 sheets. Edit → Sheet → Copy → OK. You now have 32.
Select the first 4 sheets. Edit → Sheet → Copy → OK. You now have 36.
You can probably do those steps in about a minute. Any other method will take you longer. I'm sure it took longer than a minute to write your post. It will probably take longer than a minute to download and install the macro solution, then figure out how to use it.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Duplicating formatted worksheet several times in a workb
assuming your worksheet is sheet1 and you have already created the sheets to sheet36
open sheet1
Ctrl + End
Shift + Ctrl + Home
Ctrl + C
open sheet2
Shift + Tab sheet36 (sheet tabs from 2 - 36 will turn white)
Ctrl + V
open sheet1
Ctrl + End
Shift + Ctrl + Home
Ctrl + C
open sheet2
Shift + Tab sheet36 (sheet tabs from 2 - 36 will turn white)
Ctrl + V
OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
If your question has been answered please add [solved] to the title by using the edit button at your first post
Re: [Solved}Duplicating formatted worksheet several times in
I like Mr. Programmer's solution. By the time I read it I had already copied the sheet 35 times. No idea you could select more than one, but makes sense. I had not created any other blank sheets.
I'll try to exercise more patience in the future.
I'll try to exercise more patience in the future.
Windows 10
Open Office 4.1.11
Don't want to buy Excel
Open Office 4.1.11
Don't want to buy Excel