Duplicating a spreadsheet

Open the spreadsheet in Numbers. Then from the File menu select Duplicate.

After you make the Duplicate, you can delete the values.

Then you can go to the File menu and select "Save as Template...".

Smilingjohn wrote:

then delete all the values but not the formulas and formats

You can do that part with a simple script. (Test first on a backup of your document to understand what it does.)

1. Copy-paste the script below into Script Editor.
2. Select the cells in a table where you want to remove values but leave formulas and format unchanged.
3. Click the triangle 'run' button in Script Editor.

That's it. Once you've cleaned out the values you don't want then save the document as a template for next year.

SG

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

repeat with aCell in selRng's cells

tell aCell to if its formula is missing value then ¬

set its value to ""

end repeat

end tell

end tell

end tell

Hi John,

Without more precise vocabulary, it's difficult to tell what you've done 'wrong,' as it is difficult to tell what you have actually done.

1. Copy-paste the script below into Script Editor.
   "1. I highlighted the script instructions you provided and copied it into Script"
   "the script instructions" are the three sentences at the bottom of the post (repeated here). The "Script" is the ten formatted lines beginning
   "tell application…." and ending "end tell".
   It is the Script that is to be copied, and the Script Editor (application) into which the Script is to be pasted.
   
2. Select the cells in a table where you want to remove values but leave formulas and format unchanged.
   "2. I opened the spreadsheet that is in Numbers and copied it."
   There is nothing in SG's instructions that say you are to copy the spreadsheet. SG does say you should initially test the script on a backup of your document, which may be what you are referring to here. Making the backup copy would require Duplicating the document, then working with the Duplicate copy. See Duane's post for instructions on Duplicating.
   What did you actually copy? "Spreadsheet" refers to the whole document. "Copy" means 'place a copy of (something) onto the Clipboard' (as you did in step 1).
   SG's instructions were to select the group of cells containing the data you wanted to remove (and any cells in that group that contained formulas—which would not be removed).
   You can make the selection by clicking (once) on the top left cell of the rectangular group you want to select, releasing the mouse button and moving the pointer to the bottom right cell of the group, then holding the shift key down while you click on that bottom left cell.
   
3. Click the triangle 'run' button in Script Editor.
   "3. Opened Script, clicked on the info I copied into Script and clicked on the arrow."
   The Script Editor should still be open from the step where you pasted the Script into it. You will need to bring the Script Editor to the front by clicking on it if it is visible, or by going to Mission Control and selecting it there).
   There is no need to click on (or in) the 'info you copied into Script (editor)'. Just click the green Run button, identified by the triangle pointing to the right.
   "4. Got the following message: "Numbers got an error: Can't get document 1. Invalid index"."
   Thank you for copying and including the exact error message. That will help SG (or possibly Yellowbox, who'll likely be along sooner than SG) test the script and diagnose the problem. I'm unable to do that, as the current version of AppleScript is not compatible with the earlier version of Numbers that I am using. "Can't get document 1" could mean you do not have a document open in Numbers, or that you have not selected cells in the document that is open in Numbers.

Regards,

Barry

Hi John,

Barry writes:

That will help SG (or possibly Yellowbox, who'll likely be along sooner than SG) test the script and diagnose the problem

Thanks Barry, but SG, Wayne and quinn are the AppleScript gurus!

John,

I will go with Duane's idea. As an added precaution to prevent accidental deletion of formulas (or in case someone deletes them for you) place Input data in an unlocked table, and formulas and other "must preserve" data in locked tables. For example, a document with income and expenditure for 2015.

Every table that you don't want to change is locked (shown by the little x marks when selected).

The Input (Values) table is unlocked. You can safely delete Income and Expenditure Body Cells and the formats will remain:

Now as Duane said, Save as Template for 2016.

Regards,

Ian.

HI John,

I think you're almost there. The three (really simple; don't overthink them) steps are:

1. Copy-paste the script below into Script Editor.
2. Select the cells in a table where you want to remove values but leave formulas and format unchanged.
3. Click the triangle 'run' button in Script Editor.

The error message suggests you didn't do step 2 successfully and that the script can't detect that you have a Numbers document open and have selected cells in a table there. (Copy only the script into Script Editor. Select, but don't copy, the cells in a Numbers table.) Also, make sure you don't have Numbers 2 open. As written this only works with Numbers 2.

This will not destroy formulas or affect cells that were not selected before running the script. But it will remove values in selected cells that don't have formulas. Hence the advice to work with a copy (duplicate) of your original document just in case the result is not exactly as you intended so you can redo if necessary.

SG