I have a number of diffrerent workbooks all of them have the same structure
IN each one of them I create a pivot table where I summarise some results

IN need to get these summary sheet in one sheet for presentation purposes
My basic question is how this presentation sheet will be refresed, paste
link will not work if each one of thepivot table is not refressed

Hi, Stratis

I put some more thought into your situation and remembered Ron de Bruin's
great website. This link contains various VBA code samples for consolidating
data from multiple ranges, worksheets, etc.

http://www.rondebruin.nl/copy3.htm

That should at least get all of your data into one worksheet.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a number of diffrerent workbooks all of them have the same structure
IN each one of them I create a pivot table where I summarise some results

IN need to get these summary sheet in one sheet for presentation purposes
My basic question is how this presentation sheet will be refresed, paste
link will not work if each one of thepivot table is not refressed

Thank you ron that very usefull,
However does not answer a basic question . Those pivt tables that I copy the
data in one file change. the macro should refresh all of them while in closed
files before updating the data. I am not sure this is feasible
Stratis

"Ron Coderre" wrote:

Hi, Stratis

I put some more thought into your situation and remembered Ron de Bruin's
great website. This link contains various VBA code samples for consolidating
data from multiple ranges, worksheets, etc.

http://www.rondebruin.nl/copy3.htm

That should at least get all of your data into one worksheet.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a number of diffrerent workbooks all of them have the same structure
IN each one of them I create a pivot table where I summarise some results

IN need to get these summary sheet in one sheet for presentation purposes
My basic question is how this presentation sheet will be refresed, paste
link will not work if each one of thepivot table is not refressed

Stratis...

I was thinking that you'd pull the list data from the 75 files into one
large list...
then create one pivot table from that list.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

Thank you ron that very usefull,
However does not answer a basic question . Those pivt tables that I copy the
data in one file change. the macro should refresh all of them while in closed
files before updating the data. I am not sure this is feasible
Stratis

"Ron Coderre" wrote:

Hi, Stratis

I put some more thought into your situation and remembered Ron de Bruin's
great website. This link contains various VBA code samples for consolidating
data from multiple ranges, worksheets, etc.

http://www.rondebruin.nl/copy3.htm

That should at least get all of your data into one worksheet.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a number of diffrerent workbooks all of them have the same structure
IN each one of them I create a pivot table where I summarise some results

IN need to get these summary sheet in one sheet for presentation purposes
My basic question is how this presentation sheet will be refresed, paste
link will not work if each one of thepivot table is not refressed

Ron,

Actually,
each file contains the inforrmation(chekin information) of every day for a
different location. It holds about 200 max lines per dayx 60 days=8000 lines
max.
I thought next to each spreadsheet create another sheet pivot table as a
daily summary. and then have a macro to collect "all the summary sheet in a
master summary table.

something like
sheet 1: Detailed data like Date, Dept Name
Sheet 2 in the same book Day 1, Total of staff per dept

This file is repeated x 75
then have another workbook that has 1 sheet that has all the sheet 2
information line by line. this will be just the same info but appearing in
one sheet without having to open and refresh 75 different files. LIke a pivot
of pivot

Stratis

"Ron Coderre" wrote:

Stratis...

I was thinking that you'd pull the list data from the 75 files into one
large list...
then create one pivot table from that list.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

Thank you ron that very usefull,
However does not answer a basic question . Those pivt tables that I copy the
data in one file change. the macro should refresh all of them while in closed
files before updating the data. I am not sure this is feasible
Stratis

"Ron Coderre" wrote:

Hi, Stratis

I put some more thought into your situation and remembered Ron de Bruin's
great website. This link contains various VBA code samples for consolidating
data from multiple ranges, worksheets, etc.

http://www.rondebruin.nl/copy3.htm

That should at least get all of your data into one worksheet.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a number of diffrerent workbooks all of them have the same structure
IN each one of them I create a pivot table where I summarise some results

IN need to get these summary sheet in one sheet for presentation purposes
My basic question is how this presentation sheet will be refresed, paste
link will not work if each one of thepivot table is not refressed

Maybe something like this could be used:

In this example, I'm consolidating data from 4 Excel files
(MultiLists_01.xls through MultiLists_04.xls) all located in C:\ExcelQueries\.
Those files have many lists in named ranges, but I only want to extract
rngList01 from each of them into one consolidated list.

Step_1: Build the base query
<data<import external data<new database query
Data source: Excel files
(that will open MS Query)
Select workbook: C:\ExcelQueries\MultiLists_01.xls
Add Tables: rngList01
Select all fields from that table and run the query
(but don't return the data to Excel just yet)

Step_2: (Still in MS Query) Change the base query into a union query
Click the [SQL] button to view the query
It looks like this:
SELECT rngList01.List
FROM `C:\ExcelQueries\MultiLists_01`.rngList01 rngList01

Edit it to look like this:
SELECT *
FROM `C:\ExcelQueries\MultiLists_01`.rngList01
UNION ALL
SELECT *
FROM `C:\ExcelQueries\MultiLists_02`.rngList01
UNION ALL
SELECT *
FROM `C:\ExcelQueries\MultiLists_03`.rngList01
UNION ALL
SELECT *
FROM `C:\ExcelQueries\MultiLists_04`.rngList01

Click the [OK] button
(MS Query will warn you that it can't show the query...that's fine, just
click [OK])
(Now you'll see the consolidated data)
<file<return data to Microsoft Excel

Choose the destination and you're done.

Notes:
1)The source data is in Named Ranges. That's important.
2)All of the lists must have the same column structure.
3)The source files don't need to be open in order to pull their data.
4)In that example, I consolidated ranges from 4 different Excel files into
one list. You could expand that model to consolidate many files (I ultimately
tested the model with 40 files and it worked fine)
5)All of the files may need to be in the same folder. I didn't test that.

See if that is something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Stratis...

I was thinking that you'd pull the list data from the 75 files into one
large list...
then create one pivot table from that list.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

Thank you ron that very usefull,
However does not answer a basic question . Those pivt tables that I copy the
data in one file change. the macro should refresh all of them while in closed
files before updating the data. I am not sure this is feasible
Stratis

"Ron Coderre" wrote:

Hi, Stratis

I put some more thought into your situation and remembered Ron de Bruin's
great website. This link contains various VBA code samples for consolidating
data from multiple ranges, worksheets, etc.

http://www.rondebruin.nl/copy3.htm

That should at least get all of your data into one worksheet.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a number of diffrerent workbooks all of them have the same structure
IN each one of them I create a pivot table where I summarise some results

IN need to get these summary sheet in one sheet for presentation purposes
My basic question is how this presentation sheet will be refresed, paste
link will not work if each one of thepivot table is not refressed

thks Ron, I will test it tomorow in the office,and let you know

"Ron Coderre" wrote:

Maybe something like this could be used:

In this example, I'm consolidating data from 4 Excel files
(MultiLists_01.xls through MultiLists_04.xls) all located in C:\ExcelQueries\.
Those files have many lists in named ranges, but I only want to extract
rngList01 from each of them into one consolidated list.

Step_1: Build the base query
<data<import external data<new database query
Data source: Excel files
(that will open MS Query)
Select workbook: C:\ExcelQueries\MultiLists_01.xls
Add Tables: rngList01
Select all fields from that table and run the query
(but don't return the data to Excel just yet)

Step_2: (Still in MS Query) Change the base query into a union query
Click the [SQL] button to view the query
It looks like this:
SELECT rngList01.List
FROM `C:\ExcelQueries\MultiLists_01`.rngList01 rngList01

Edit it to look like this:
SELECT *
FROM `C:\ExcelQueries\MultiLists_01`.rngList01
UNION ALL
SELECT *
FROM `C:\ExcelQueries\MultiLists_02`.rngList01
UNION ALL
SELECT *
FROM `C:\ExcelQueries\MultiLists_03`.rngList01
UNION ALL
SELECT *
FROM `C:\ExcelQueries\MultiLists_04`.rngList01

Click the [OK] button
(MS Query will warn you that it can't show the query...that's fine, just
click [OK])
(Now you'll see the consolidated data)
<file<return data to Microsoft Excel

Choose the destination and you're done.

Notes:
1)The source data is in Named Ranges. That's important.
2)All of the lists must have the same column structure.
3)The source files don't need to be open in order to pull their data.
4)In that example, I consolidated ranges from 4 different Excel files into
one list. You could expand that model to consolidate many files (I ultimately
tested the model with 40 files and it worked fine)
5)All of the files may need to be in the same folder. I didn't test that.

See if that is something you can work with?

Post back with any questions.
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Stratis...

I was thinking that you'd pull the list data from the 75 files into one
large list...
then create one pivot table from that list.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

Thank you ron that very usefull,
However does not answer a basic question . Those pivt tables that I copy the
data in one file change. the macro should refresh all of them while in closed
files before updating the data. I am not sure this is feasible
Stratis

"Ron Coderre" wrote:

Hi, Stratis

I put some more thought into your situation and remembered Ron de Bruin's
great website. This link contains various VBA code samples for consolidating
data from multiple ranges, worksheets, etc.

http://www.rondebruin.nl/copy3.htm

That should at least get all of your data into one worksheet.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"stratis" wrote:

I have a number of diffrerent workbooks all of them have the same structure
IN each one of them I create a pivot table where I summarise some results

IN need to get these summary sheet in one sheet for presentation purposes
My basic question is how this presentation sheet will be refresed, paste
link will not work if each one of thepivot table is not refressed