Results 1 to 4 of 4

Thread: Can you create a new spreadsheet from an old one?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Location
    Las Vegas, NV
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Can you create a new spreadsheet from an old one?

    I am not an Excel person; however, I have a quarterly report that is in Excel and consists of 2 pages. I inherited the report when we started telecommuting and we lost our administrative support.

    The report's design was sent to me and it worked well for 1 fiscal year. Because it keeps track of the amount of funds that are billed on a particular contract, instead of sending me a new spreadsheet for the next year, they (my client) decided to just keep going on this particular spreadsheet because they were extending the contract. I copied and pasted the spreadsheet, but I had to go through and take out all the numbers, change all the dates, etc. Should be easy right? Page two of the spreadsheet takes the numbers from page two and sums up everything. I messed this up this last Fiscal year and now they want to continue it again.

    Can this process be made simpler? The spread sheet is now 4 pages (2 for year 2016 and 2 for year 2017). I'm willing to share the sheets with anyone that wants to look at it and help me out.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,041
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can you create a new spreadsheet from an old one?

    Making a template would be the route to go. To make a template, save the file with the two sheets (I assume that is what you meant by pages) under a different name. Use the new file to set up the template. Do Not delete any data when a formula appears in the display box at the top of the screen and do not delete your headings or other static data which will not change throughout the year. Delete only the data that matches what appears in the display box. This should leave your worksheets with only headings and formulas which can be used for a template. When you need a file, simply do a SaveAs for the template to the FY report name you want to use.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Jan 2018
    Location
    Las Vegas, NV
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Can you create a new spreadsheet from an old one?

    I tried that last year, but the links disappeared and I had to try to link them up again. I did a bad job of this. Since the spreadsheet is not 4 pages, is there a way to make a template of the original 2 pages and keep them linked, or do I have to go back to the original spreadsheet that was sent to me in 2016?

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,041
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Can you create a new spreadsheet from an old one?

    The formulas are what links the data. That is why formulas should NOT be deleted. Only delete the data when the fromula bar display shows the data value the same as is displayed in the cell. Those are the cells which either are filled by external source or manually. The formulas then use that data to populate the other cells. I don't know that I could write code to do that for you because of the headings and other static entries that might be in the worksheets. Surely there is someone you work with who can assist you to prevent the loss of your formulas and links. It would be extremely difficult to create a template for you from remote locations without having your file and I don't accept files by personal email for security reasons. But there are people smarter than me who monitor this site and they might have a better solution.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •