News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Clone an Excel sheet

    Posted on petermat Comment on the AskWoody Lounge
    Topic Resolution: Resolved


    • This topic has 4 replies, 3 voices, and was last updated 3 months ago by anonymous.
    Viewing 4 reply threads
    • Author
      • #2213940 Reply
        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 Reply
        Da Boss

        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 Reply
        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 Reply

        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 Reply

        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, and politics/religion are relegated to the Rants forum.

    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.