News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • Clone an Excel sheet

    Topic Resolution: Resolved


    Viewing 4 reply threads
    • Author
      • #2213940
        AskWoody Plus

        However by “Clone” I mean copy all text and all formula, but no data – or perhaps data entries replaced by zero – into a blank sheet.

      • #2213954

        Not being sure of how your source spreadsheet is set up, have you tried copying the worksheet (Select All first), copying it, and using Paste Special to paste the formula and formats into a new worksheet?

      • #2213965
        AskWoody MVP

        Right-click the name of the spreadsheet on the bottom, select Move or Copy, in the dialog box that pops up, click the drop-down and select (new book), then below the Before sheet box put a check in the box beside Create a copy, and click OK.

        A new book will be created, the open sheet will be an exact copy of the sheet you wish to clone.  You will then have to select the data you wish to remove, right-click and select Clear contents.  All formulas will be left in place.

        Create a fresh drive image before making system changes/Windows updates, in case you need to start over!
        "When you're troubleshooting, start with the simple and proceed to the complex."—M.O. Johns
        "Experience is what you get when you're looking for something else."—Sir Thomas Robert Deware

      • #2214021

        Thanks – but I am afraid neither of those options work for me.

        The trouble with paste special is that it does not distinguish between text – that I need to keep – and numerical data – which I want deleted or replaced by zero.

        Granted the copy and manually delete unwanted data works in principle, but in practice there would be many hours of work involved, and because of this, the risk of error is unacceptably high.

        Any other suggestions – please!

      • #2214164

        OK, I see a way forward here. Copy everything from the entire sheet. Then eliminate the numeric values by successively replacing “.” by “”, “1” by “0”, “2” by “0” etc. Done. This works whether the number was entered with a numeric, general, or text format. It does rely on there being no digits in the text that is to be kept, and does not deal with notes or comments, so I think a lot more could be added to paste special, but this will get me by.

        Thanks again.

        1 user thanked author for this post.
    Viewing 4 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: Clone an Excel sheet

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.