Copy Cells from various workbooks to central workbook

[Chewy_Oz]

Hi,

I'm trying to create a global workbook that creates a summary from various other workbooks. The aim of this is to produce a report from team members WIP lists that can be viewed at weeking team meetings. On opening the global workbook, it will initiate a macro to gather data located in a specific range from the other workbooks and copy it into the global one.

The problem I am having is that the code I'm writing in the global workbook doesn't recognise the other workbooks. I've managed to open those workbooks but am unsure as to how to either run the marcos for those workbooks to gather the data, or just to navigate round the other workbooks from the global one.

Does anyone have any example code that is similar to this that I can view?

Cheers
Chewy

[ravishankar]

Hi
How many and where are these "other files" located. What is the range of data you want to summarize and how do you want it displayed in the global workbook?
Ravi

[Richard Buttrey]

Hi,

I'm trying to create a global workbook that creates a summary from various other workbooks. The aim of this is to produce a report from team members WIP lists that can be viewed at weeking team meetings. On opening the global workbook, it will initiate a macro to gather data located in a specific range from the other workbooks and copy it into the global one.

The problem I am having is that the code I'm writing in the global workbook doesn't recognise the other workbooks. I've managed to open those workbooks but am unsure as to how to either run the marcos for those workbooks to gather the data, or just to navigate round the other workbooks from the global one.

Does anyone have any example code that is similar to this that I can view?

Cheers
Chewy

This is a fairly standard requirement. Assuming you open the workbooks one by one, closing one down before opening the next, the standard approach I use is to create two Workbook variables. e.g.

Dim wbMyWB as Workbook, wbTempWB as Workbook

At the beginning of the macro that starts to open the daughter workbooks, use the code "Set wbMyWB = ActiveWorkbook", and then immediately the macro has opened use a similar line of code "Set wbTempWB = ActiveWorkbook". Now you can happily toggle between the two with either "wbMyWb.Activate", or "wbTempWb.Activate". When you've finsihed processing the daughter workbook, use the "wbTempWb.Close" line of code.

Navigating around the daughter workbooks, assuming they are the activeworkbook is just the same as the global workbook. The lines of code in the global workbook will act on the daughter workbook. If the daughter workbook is not the Activeworkbook, you can still do most of the processing you need provided you include the name of the daughter workbook, (or its variable name assuming you've set it as above) in any instruction.


Rgds

[Chewy_Oz]

Thanks for that, I'm able to toggel between workbooks, but can't seem to to be able to select certain worksheets within the daughter workbooks. here is my code:

Please Login or Register  to view this content.

when it hits the sheets(1).select in the subroutine it creates the error: run time error 40036.

The same line of caode works when the master workbook is active, just not when the daughter is activated.

How do I activate specific worksheets within the daughter workbook?

Thanks