+ Reply to Thread
Results 1 to 6 of 6

Clone worksheet from one workbook to another?

  1. #1
    Registered User
    Join Date
    08-17-2010
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Clone worksheet from one workbook to another?

    I have roughly seventy workbooks all with multiple sheets (anywhere from 2 to over 500). Every workbook has a summary page that pulls data from all sheets in the book. What I would like to do, is create another workbook as a "master summary". In this I would like to have all the summary sheets from my other workbooks be the worksheets in this master summary so that I could create a single worksheet that has a grand total of all my data.

    I know how to copy these summary worksheets into my master summary and have the data update automatically every time I open it. However this only works if I keep all the rows and columns of my seventy some summary sheets the same.

    My problem is that as time goes on I will need to add/remove rows and columns, switch them around, etc... on these individual summaries. Is there a way to make a workbook with worksheets that just "clone" a worksheet in another workbook every time you open it? Basically I need to be able to make a worksheet that can automatically pull every cell with data in it from a sheet in another workbook regardless of rows and columns being moved, added, or deleted.

    Thanks for any help.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    Re: Clone worksheet from one workbook to another?

    Hi,

    In order to establish the most efficient way of doing this we need to see three or four of your typical summary sheets, with rows / columns added and containing examples of how your data may be switched around. i.e. we need t see what the summary sheets may look like in due course.

    We don't need all 500 supporting sheets in each workbook so just take copies of the summary sheets and convert the formulae to values before uploading them.

    Rgds
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    08-17-2010
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Clone worksheet from one workbook to another?

    Here is an attached sample of what they all look like with made up names and numbers. This is just a small one for the sake of example, but like I said the number of people (rows) varies greatly from just a few to hundreds.

    Edit: As far as how I will need to switch data around, I might need to remove and add participants (rows) and it is possible that new money sources (safeharbor, profit sharing, rollover, loans, match) will need to be added in future years; these are the columns. I would like to be able to get data pulled from new rows and columns without having to manually add them on my linked workbook.
    Attached Files Attached Files
    Last edited by csulli; 08-17-2010 at 01:51 PM.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    Re: Clone worksheet from one workbook to another?

    OK, first questions.

    Do the same Participants occur on several summaries, and if so are you wanting to summarise by Participant?

    Or are you simply wanting to pick up all the entry rows from each summary and line them up, one section under the other, allowing of course for any changed column positions.

    Are the column labels consistent across summaries?

    Rgds

  5. #5
    Registered User
    Join Date
    08-17-2010
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Clone worksheet from one workbook to another?

    No each summary has different participants. All I really need is a page that can update data when I open it that has the totals row from each of my seventy workbook summaries as rows which I can then total at the bottom. I do use the same column labels across all the summaries, however not all the summaries have all the columns if that makes sense. For example the attached summary only has EE columns instead of EE and ER columns. On the master summary, I would total all EE columns across all summaries, all ER columns, all Rollover columns, etc... but not every summary has every kind of column. Some only have EE, some have five different sources, but the labeling is always consistent and so is the order of the columns.
    Attached Files Attached Files

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,952

    Re: Clone worksheet from one workbook to another?

    Hi,

    You're going to need a macro to do this.
    It would be much easier if you made sure all the summary sheets had the same layout of column headers with blank columns if necessary.
    How are you defining your totals row? Is it always called 'Ending Balance' or 'Statement Balance', and always in column B?

    The general idea would be to either list all your 70 workbook names so that a macro could loop through them all, open the workbook, find the summary total row in each workbook, copy and paste it to the summary workbook then close the workbook and loop to the next workbook.

    If the column headers are not in exactly the same position then you'll need to introduce some additional code that can recognise the column label row, compare it with a master row of labels that contains every single column label and resorts the columns into the same order before the copying & pasting takes place.

    That's the approach I'd take.

    HTH

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1