Excel - Worksheet: Duplicating a worksheet

Scripting Forums AppleScript | Mac OS X
,
#1

Hey all,

Anyone have code to duplicate a worksheet in excel?

thanks

#2

thanks works great.

#3

Using:


tell application "Microsoft Excel"
	activate
	copy worksheet sheet "WorksheetName" after last sheet
end tell

Question:

  1. How do you specify the name of the new worksheet to say “Dup1”?

thanks

#4

the straightforward solution:


tell application "Microsoft Excel"
	--	activate -- actually not needed
	copy worksheet sheet "WorksheetName" after last sheet
	set name of last sheet to "Dup1"
end tell
#5

Thanks for the direction, Unfortunately.

if you use just

it works

but if you use the code above applescript editor gives you the following error

Microsoft Excel got an error: Can't set name of last sheet to "Dup1".
#6

Of course I tested the script on my machine (G5 Dual, Excel 2004).
It worked fine. Maybe you use the snippet in an envorinment, which changes the references

#7

I’m copying a sheet to the middle (not the end) of my stack of sheets:

Sheets: General, Account1, Account2, Account3

The script below works fine to create the sheet at the desired location:

tell application “Microsoft Excel 2004”
copy worksheet worksheet “General” after sheet “Account1”
end tell

How do I name a new sheet in the middle, not knowing which one to address? I’d like to add “…with properties {name:“New Account”}” to the above copy command, but I get an error when I try to compile that.

I thought of trying to set the new sheet as a variable “newAccountSheet” which I could refer back to but couldn’t make that work either…

tell application “Microsoft Excel 2004”
set newAccountSheet to copy worksheet worksheet “General” after sheet “Account1”
set name of worksheet newAccountSheet to “New Account”
end tell

Any ideas?

Model: G5
AppleScript: 1.10.7
Browser: Safari 531.9
Operating System: Mac OS X (10.4)

#8

Hi,

copy worksheet doesn’t return a result,
but logically the index of the new created worksheet is the index of the worksheet “Account1” +1


tell application "Microsoft Excel"
	copy worksheet worksheet "General" after sheet "Account1"
	set newAccountsheet to worksheet ((entry index of worksheet "Account1") + 1)
	set name of newAccountsheet to "New Account"
end tell
#9

Just an FYI:

Are their any charts or pivot tables in the sheet to be copied?

If so, the COPY command works so that the chart on the new sheet will pull its data from the same source as the chart on the old sheet. The source data of the copied chart needs to be adjusted to pull from the copied (rather than original) sheet.

I don’t know for sure, but I suspect copied Pivot Tables will show similar behaviour.