Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Loop through each worksheet (79/2000)

    Hi All

    I have been given a little project to get on with and because of financial constraints have suggested that we look at an Office soloution compared with a web/database solution.

    Background.

    1) A number of people, which can range between 10 -30, will be provided with an Excel template that will all be held on a shared drive.

    2) A project manager will then be able to consolidate all these templates into one workbook

    3) The project manager will then consolidate the values in each worksheet of the workbook created in 2) to create a new worksheet that sums up all the values


    My Problem

    1) and 2) have been solved but 3) is my problem.

    The number of project templates may vary from day to day and so I therefore need to find a way of varying the consolidation in 3).

    a) Should I

    a) create a piece of code that loops through each worksheet in 3) summing the required fields and if so how do I loop through each worksheet, I tried For Each Worksheet in Workbook, but it did not like it!!!

    [img]/forums/images/smilies/cool.gif[/img] Think of a different way?


    Jerry
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop through each worksheet (79/2000)

    You could perhaps do it as follows:

    Insert a blank worksheet named Start before the sheets filled in by the users, and a blank worksheet named End after those sheets. The sheet on which the data will be consolidated should NOT be in between Start and End.
    On the consolidation worksheet, use formulas such as =SUM(Start:End!A2:A100)
    Since Start and End are blank themselves, they contribute nothing; they act as placeholders. You can insert and delete any number of worksheets in between them, the formulas will stay valid.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop through each worksheet (79/2000)

    Thanks Hans

    I will have a play with this during the day. On first look it does look a viable and much simpler solution than my "loop through worksheets" option. I will get back to the board if their are any problems.

    Jerry
    Jerry

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop through each worksheet (79/2000)

    Hans

    I thought there would be problems.

    I have reused some old code and adapted it to fit this problem. You will note that in the attachment I have a worksheet called main. If you run the macro called combine, it currently asks you to locate a folder on your drives which contains the workbooks you want to consolidate. ( This will be hard coded to a specific location once I have sorted the application out). No Problems.

    The code creates a new workbook , adds a sheet called Start, appends all the worksheets from the workbooks in the folder and then adds a sheet to the end (called End, funnily enough), saves the workbook, job jobbed.

    As you know I tend to go for the KISS principle wherever possible.

    Do you think I should have a seperate workbook that the project manager can have on his PC which is linked and when he opens it the links can be refreshed allowing him to reset the link to the newly created consolidated workbook. If so I have tried the formula you gave me above and it does not like when I try to link it to the external file.

    Or is there away that the code creates the Start, End Sheets and sandwiched sheets inside the attached excel workbook. I tried changing the code to say worksheet.add instead of workbook.add but didn't import the external excel files....am I missing something here?
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop through each worksheet (79/2000)

    To add a worksheet, you use Sheets.Add or Worksheets.Add, not Worksheet.Add (you have Sheets.Add elsewhere in your code). You can use

    Set wbkTarget = ThisWorkbook ' or ActiveWorkbook
    Sheets.Add.Name = "Start"

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop through each worksheet (79/2000)

    If you are going to use Hans' suggestion, then I would make a suggestion here. In your code that consolidates all of the workbooks, don't use Workbooks.Add to create a new workbook for the consolidation. Instead, create a new workbook that contains one worksheet. That worksheet would have all of the formulas like the one Hans suggested to sum the sheets. Save this workbook as a Template. Then in your code where you create the new workbook, just open the template file and do what you are doing to copy the sheets from the other workbooks. You should end up with a workbook that has the summary as the first sheet with all the others following.
    Legare Coleman

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Loop through each worksheet (79/2000)

    Hi Legare

    Thanks for this. I picked that up while I was creating the code.

    You can see from my original Excel sheet that I sent through used one Worksheet called Main. The problem was that I could not hold the formulas in this sheet as they would not refresh when the new book was created. I have got around that problem now by having three sheets Main, Start and End. Referencing the consolidation formula in Main to the other two sheets. Run the code and then moved the End Worksheet to the end afterwards. This keeps the references.

    I have attached it to see my result.

    Jerry
    Jerry

Posting Permissions

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