1) All the Assumptions are perfect (except clear/erase the database as I
would start with previous month merged database-See Option (a) )

2) The revised submission also will be in the same format. I would be
receiving this by email (after discussions /corrections) and manually placing
it in the designated folder. I would use the option (b) explained as under
for merging the database.

3) The macro should provide 2 options
(a) Batch Processing : All files in the Folder will be read and merge the
€œDATABASE€� Tab to a New Workbook. If the location code is present in the
merged database, those rows to be deleted first and replaced. I should have
the flexibility of using this option more than once.

(b) Interactive Processing : When this option is selected, Excel should
prompt for the file to be chosen for merging the €œDatabase€� Tab in the
revised submission. The existing rows pertaining to location code to be
deleted from the MERGED DATABASE and then replaced with the new database.

Thanks for your efforts.


"Otto Moehrbach" wrote:

I'll assume that the VBA code will be located in the
Location_Summary_July09.xls workbook. I'll also assume, from what you say,
that the monthly reports will always be located in the same folder as the
Location_Summary_July09.xls workbook. If that is true, then the name of the
folder is irrelevant.

You say that the data to be copied will always be in the DATABASE sheet of
those monthly reports.

I will assume that the Location_Summary_July09.xls workbook has only one
sheet.

I will assume that all the monthly reports are .xls files.

I will write the macro to do the following. in order.

Clear (erase) the entire sheet in the Location_Summary_July09.xls workbook
from row 2 down to the last row that has data in Column A.

Loop through all the workbooks in the folder that holds the workbook that
has the VBA code, with the sole exception of the one workbook that has
"Location_Summary" as the first 16 characters in its name.

With each workbook, the code will:

Open the workbook.

Copy (How many columns?) the DATABASE sheet from row 2 down to the last
occupied cell in Column A.

Paste this into the first blank cell in Column A of the one sheet in the
Location_Summary_July09.xls workbook.

Close the monthly workbook.



I understand how you want to handle a "revised submission". But how is a
revised submission different from any other monthly report? In other words,
how do YOU know that a report is a revised submission when you are doing all
this by hand? Otto

"Ananth" wrote in message
...
1) Folder changes for each month : Yes
2) No other files will be in this folder.
3) Consolidated Worksheet is a Separate Workbook called :
Location_Summary_July09.xls. Located in the Same folder.
4) 53rd Tab is called "DATABASE", Submissions from 60 locations that has
53rd tab "DATABASE" is to be merged into a single database in a separate
Workbook : Location_Summary_July09.xls
5) When a revised submission is rec'd, the macro should delete the
existing
contents in the merged database , by deleting all the entries having the
location code and replace with contents from Database tab from the
submission.
6) The database has a column for Date_update, which keeps track of version
change,
7) I have taken necessary precaution to camouflage data names and I have a
different name in office
8) I look forward to the solution
Thanks in advance

"Otto Moehrbach" wrote:

You say:

As I receive the reports by mail, the 60 monthly reports are placed in a

dedicated folder : :MReports_July09

With the month and year in the name of this "dedicated" folder, I take it
that the folder changes for each month. Is that correct?

Will there ever be any files (workbooks, etc) in that folder that are not
the monthly reports that the macro should work with?

This "Consolidated Worksheet" into which you want to "merge" data, is
that a
separate workbook? If so, in what folder will it be located?

You say you want the data from the 53rd tab of each monthly report
"merged"
into the "Consolidated_DB? What is the name of this 53rd worksheet?
What
do mean when you say "merge"? Do you want a simple copy/paste done? Or
is
it more involved?

In item 2) of your post, you mention "the revised submission" and how it
should be merged. Do you want a search done for each Location Code, and
if
found in the Consolidated_DB, delete it from the Consolidated_DB and copy
over the new one? Do you want this done one at a time or can the code
simply delete all entries having that Location Code? And how will the
code
know that this one monthly report is a revised submission and not an
initial
monthly report?

As you answer these questions, remember that no one reading
your
post works in your office nor knows anything about your office. Please
use
generic terminology whenever possible. HTH Otto

"Ananth" wrote in message
...


I receive data from warehouse in charge in excel which has 52 tabs of
information. Key information is captured in the 53rd Tab called
"Database"
This database has 500 rows and 26 Columns of Information. Has a header
at
A1
to Z1.

Column A represents Location Code (5 digit : example : "US001")

This is a monthly report submitted by Global Managers at 60 locations
This
is a uniform report and the Database Tab is password protected.

As I receive the reports by mail, the 60 monthly reports are placed in
a
dedicated folder : :MReports_July09

I sitting at WhqHQ, would merge all the database in a Consolidated
Worksheet
manually and start analyzing., prepare reports which are all
standardized.

1) I require a Merge Macro which will consolidate the "DATABASE" into
one
Consolidated_DB,

2) As and when I receive the revised submission from a LOCATION, the
macro
should merge the revised DATABASE and delete the old one (Key :
Location
Code
in Column "A")

Any help is appreciated.