I just tried and am not a programmer but I'm sure this will take less than an
hour of somebody's time. Basically I have spreadsheets (6) that need to be
automatically loaded into another ss each month by country. We're just
linking to cell references now so if one country gets added in the whole
thing gets thrown off & has to be re-linked... I just want something simple
and really need some help. Can you email if you can help? Happy to offer
some renumeration for your time. This is Excel 2000. Thank you.

Try the macro below: Assumes that all the files are in one folder, are only one sheet, and the data
starts in cell A1 and have no blanks in column A in the data range.

If my assuptions don't match your actual situation, post back with a better description of the
structure of your workbooks.

And thanks for the offer of "renumeration" for my time, but I like how my time is numbered just
fine... ;-)

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = Workbooks.Open(.FoundFiles(1))
For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("a1").End(xlDown).Off set(1, 0)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls")
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub

" wrote in message
...
I just tried and am not a programmer but I'm sure this will take less than an
hour of somebody's time. Basically I have spreadsheets (6) that need to be
automatically loaded into another ss each month by country. We're just
linking to cell references now so if one country gets added in the whole
thing gets thrown off & has to be re-linked... I just want something simple
and really need some help. Can you email if you can help? Happy to offer
some renumeration for your time. This is Excel 2000. Thank you.

Thanks, Bernie - I'm going to try this tonight...

"Bernie Deitrick" wrote:

Try the macro below: Assumes that all the files are in one folder, are only one sheet, and the data
starts in cell A1 and have no blanks in column A in the data range.

If my assuptions don't match your actual situation, post back with a better description of the
structure of your workbooks.

And thanks for the offer of "renumeration" for my time, but I like how my time is numbered just
fine... ;-)

HTH,
Bernie
MS Excel MVP

Sub Consolidate()
With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = Workbooks.Open(.FoundFiles(1))
For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i))
Range("A1").CurrentRegion.Copy _
Basebook.Worksheets(1).Range("a1").End(xlDown).Off set(1, 0)
myBook.Close
Next i
Basebook.SaveAs Application.GetSaveAsFilename("Consolidated file.xls")
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With


End Sub

" wrote in message
...
I just tried and am not a programmer but I'm sure this will take less than an
hour of somebody's time. Basically I have spreadsheets (6) that need to be
automatically loaded into another ss each month by country. We're just
linking to cell references now so if one country gets added in the whole
thing gets thrown off & has to be re-linked... I just want something simple
and really need some help. Can you email if you can help? Happy to offer
some renumeration for your time. This is Excel 2000. Thank you.

I dont know if this will help. I have 14 workbooks that link
If I have all 14 open and add or delete a column or row from one sheet all
links are automatically changed in the others. just remember to save changes
to all files.
--
TJLV


" wrote:

I just tried and am not a programmer but I'm sure this will take less than an
hour of somebody's time. Basically I have spreadsheets (6) that need to be
automatically loaded into another ss each month by country. We're just
linking to cell references now so if one country gets added in the whole
thing gets thrown off & has to be re-linked... I just want something simple
and really need some help. Can you email if you can help? Happy to offer
some renumeration for your time. This is Excel 2000. Thank you.

Hi -

Thank you for your post!

How do you do this? Basically the data comes out like this (approx 20-25
countries. The Category column values remain the same & repeat over & over).
There are around 6 files with different kinds of data all with the same
layout (but the values in the category file might be different, so countries
end up on different lines in each):

Country | Category | Jan1995 | Feb1995 | ... | CurrentDate|

Example: Data Source SS1
A1: Australia | Oil | 200 | 60 | 80 | ... |75|
A2: Australia | Gas | 100 | 90 | 85 | ... |95|
A3: Austria | Oil | 82 | 66 | 73 | ... |92|

Example: Data Source SS2
A1: Australia | Fuel | 120| 150 | 140 | ... |150|
A2: Austria | Fuel | 102 | 106 | 107 | ... |109|

So all Australia numbers would go in one sheet, with sections separated by
the data sources.

Does this make sense?

"TJLV" wrote:

I dont know if this will help. I have 14 workbooks that link
If I have all 14 open and add or delete a column or row from one sheet all
links are automatically changed in the others. just remember to save changes
to all files.
--
TJLV


" wrote:

I just tried and am not a programmer but I'm sure this will take less than an
hour of somebody's time. Basically I have spreadsheets (6) that need to be
automatically loaded into another ss each month by country. We're just
linking to cell references now so if one country gets added in the whole
thing gets thrown off & has to be re-linked... I just want something simple
and really need some help. Can you email if you can help? Happy to offer
some renumeration for your time. This is Excel 2000. Thank you.

"Country + Category" is unique on each sheet and not repeated in each
workbook?
then copy all of them in same workbook, sort by country will do?

" wrote in
message ...
Hi -

Thank you for your post!

How do you do this? Basically the data comes out like this (approx 20-25
countries. The Category column values remain the same & repeat over &
over).
There are around 6 files with different kinds of data all with the same
layout (but the values in the category file might be different, so
countries
end up on different lines in each):

Country | Category | Jan1995 | Feb1995 | ... | CurrentDate|

Example: Data Source SS1
A1: Australia | Oil | 200 | 60 | 80 | ... |75|
A2: Australia | Gas | 100 | 90 | 85 | ... |95|
A3: Austria | Oil | 82 | 66 | 73 | ... |92|

Example: Data Source SS2
A1: Australia | Fuel | 120| 150 | 140 | ... |150|
A2: Austria | Fuel | 102 | 106 | 107 | ... |109|

So all Australia numbers would go in one sheet, with sections separated by
the data sources.

Does this make sense?

"TJLV" wrote:

I dont know if this will help. I have 14 workbooks that link
If I have all 14 open and add or delete a column or row from one sheet
all
links are automatically changed in the others. just remember to save
changes
to all files.
--
TJLV


" wrote:

I just tried and am not a programmer but I'm sure this will take less
than an
hour of somebody's time. Basically I have spreadsheets (6) that need
to be
automatically loaded into another ss each month by country. We're
just
linking to cell references now so if one country gets added in the
whole
thing gets thrown off & has to be re-linked... I just want something
simple
and really need some help. Can you email if you can help? Happy to
offer
some renumeration for your time. This is Excel 2000. Thank you.

No, actually the unique value is the file name (refinery, demand, stocks,
etc+country+type) I'm open to copying & pasting each file into a workbook
each time & adding another column w/the file type (ie, refinery, etc.)...if
it's necessary to create 1 file & upload.

"PY & Associates" wrote:

"Country + Category" is unique on each sheet and not repeated in each
workbook?
then copy all of them in same workbook, sort by country will do?

" wrote in
message ...
Hi -

Thank you for your post!

How do you do this? Basically the data comes out like this (approx 20-25
countries. The Category column values remain the same & repeat over &
over).
There are around 6 files with different kinds of data all with the same
layout (but the values in the category file might be different, so
countries
end up on different lines in each):

Country | Category | Jan1995 | Feb1995 | ... | CurrentDate|

Example: Data Source SS1
A1: Australia | Oil | 200 | 60 | 80 | ... |75|
A2: Australia | Gas | 100 | 90 | 85 | ... |95|
A3: Austria | Oil | 82 | 66 | 73 | ... |92|

Example: Data Source SS2
A1: Australia | Fuel | 120| 150 | 140 | ... |150|
A2: Austria | Fuel | 102 | 106 | 107 | ... |109|

So all Australia numbers would go in one sheet, with sections separated by
the data sources.

Does this make sense?

"TJLV" wrote:

I dont know if this will help. I have 14 workbooks that link
If I have all 14 open and add or delete a column or row from one sheet
all
links are automatically changed in the others. just remember to save
changes
to all files.
--
TJLV


" wrote:

I just tried and am not a programmer but I'm sure this will take less
than an
hour of somebody's time. Basically I have spreadsheets (6) that need
to be
automatically loaded into another ss each month by country. We're
just
linking to cell references now so if one country gets added in the
whole
thing gets thrown off & has to be re-linked... I just want something
simple
and really need some help. Can you email if you can help? Happy to
offer
some renumeration for your time. This is Excel 2000. Thank you.

You did not mention anything about file name and/or file type.
We are confused.

We do not keep watching this news group, also because of time zone,
would you like to send mails to us direct please?

" wrote:

No, actually the unique value is the file name (refinery, demand, stocks,
etc+country+type) I'm open to copying & pasting each file into a workbook
each time & adding another column w/the file type (ie, refinery, etc.)...if
it's necessary to create 1 file & upload.

"PY & Associates" wrote:

"Country + Category" is unique on each sheet and not repeated in each
workbook?
then copy all of them in same workbook, sort by country will do?

" wrote in
message ...
Hi -

Thank you for your post!

How do you do this? Basically the data comes out like this (approx 20-25
countries. The Category column values remain the same & repeat over &
over).
There are around 6 files with different kinds of data all with the same
layout (but the values in the category file might be different, so
countries
end up on different lines in each):

Country | Category | Jan1995 | Feb1995 | ... | CurrentDate|

Example: Data Source SS1
A1: Australia | Oil | 200 | 60 | 80 | ... |75|
A2: Australia | Gas | 100 | 90 | 85 | ... |95|
A3: Austria | Oil | 82 | 66 | 73 | ... |92|

Example: Data Source SS2
A1: Australia | Fuel | 120| 150 | 140 | ... |150|
A2: Austria | Fuel | 102 | 106 | 107 | ... |109|

So all Australia numbers would go in one sheet, with sections separated by
the data sources.

Does this make sense?

"TJLV" wrote:

I dont know if this will help. I have 14 workbooks that link
If I have all 14 open and add or delete a column or row from one sheet
all
links are automatically changed in the others. just remember to save
changes
to all files.
--
TJLV


" wrote:

I just tried and am not a programmer but I'm sure this will take less
than an
hour of somebody's time. Basically I have spreadsheets (6) that need
to be
automatically loaded into another ss each month by country. We're
just
linking to cell references now so if one country gets added in the
whole
thing gets thrown off & has to be re-linked... I just want something
simple
and really need some help. Can you email if you can help? Happy to
offer
some renumeration for your time. This is Excel 2000. Thank you.