[Solved] Duplicating formatted worksheet several times

Discuss the spreadsheet application
Post Reply
sigsky
Posts: 23
Joined: Fri Sep 18, 2015 4:38 pm

[Solved] Duplicating formatted worksheet several times

Post by sigsky »

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
Attachments
scorebook partial page 1
scorebook partial page 1
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
User avatar
stickman68
Posts: 4
Joined: Mon Feb 19, 2018 1:13 am
Location: NY, NY

Re: Duplicating formatted worksheet several times in a workb

Post by stickman68 »

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
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Duplicating formatted worksheet several times in a workb

Post by MrProgrammer »

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 sheet. Edit → Sheet → Copy → OK. You now have 2.
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).
coray80
Volunteer
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

Re: Duplicating formatted worksheet several times in a workb

Post by coray80 »

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
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
sigsky
Posts: 23
Joined: Fri Sep 18, 2015 4:38 pm

Re: [Solved}Duplicating formatted worksheet several times in

Post by sigsky »

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.
Windows 10
Open Office 4.1.11

Don't want to buy Excel
Post Reply