Combining Multiple Excel files with multiple pages 1

[kylebellamy]

Hello all,

I have roughly 10 spreadsheets with 10 pages in each that I use for reporting on various aspects of my companies documents server.

I was wondering if there was a way to combine them all into a super-workbook so that each of the files is a main page with sub pages beneath it. I hope that makes sense.

By way of example: Human Resources is one file. In this file there are 10 sections, each of which has a listing of all of the files and their metadata, duplicates, links to the files and other data.

Then there is Finance and Accounting. Same thing for that section and so on.

The reason for this need is to be able to present it all in one file to the higher ups as well as having a section that I maintain with just numbers to show progress and such.

Thanks,
Kyle

http://www.kb-design-studios.com

 

[SkipVought]

Hi,

You have 10 WORKBOOKS each with 10 WORKSHEETS. (Workbooks do not have pages)

Open all workbooks, and in each workbook, move or copy each sheet to your super workbook.

If you care to, you could turn on your macro recorder and record doing this one time and then modify to do the whole shabang. If you need help with the VBA code, please post in Forum707.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[kylebellamy]

Will this allow "sub worksheets"? Is that even possible? I don't want 100 worksheets all in a row. Talk about confusing management!

http://www.kb-design-studios.com

 

[SkipVought]

There is no such thing as a sub workbook or sub sheets.

Has you considered a database like MS Access?

What's wrong with 100 worksheets? You could build in some functionality (via macros) that would HIDE all the "sub sheets" and then make each "sub sheet" group visible when the "parent" sheet is activated, and hide the "sub sheet" group when the "parent is deactivated.


Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[kylebellamy]

Ah! That's the sort of thing that I was looking for. I have limited (but building) knowledge about excel. I'll have to look into building that sort of macro. Thanks.

http://www.kb-design-studios.com

 

[SkipVought]

Turn on your macro recorder and record hiding a sheet and making a sheet visible. Two separate macros.

Post back in Forum707 with your macro/VBA questions. You'll get lots of help.

Consider making a sheet that describes the groups, with a table like...
[tt]
GroupName SheetName
HR HR Sheet1
HR HR Sheet2
FIN FIN Sheet1
FIN FIN Sheet2
....
[/tt]
So if the user selects the HR sheet, the table tells you to make HR Sheet2 and HR Sheet2 visible using lookup functions.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}