You give me a fat head with all that praise. Thanks. I like to help people
and I was able to help you so I had a good day. Otto
"Ananth" wrote in message
...
Many many thanks. I made minor corrections and made it to run to my
requirement.
It has made my life less miserable and has, overnight improved my
productivity.
I am short of words to express my gratitude and joy over your help.

Long live Otto

"Otto Moehrbach" wrote:

Here is my first shot at it. Paste all the code below into a regular
module. Include the declarations at the top. Note the last declaration:

Const TheSht As String = "Otto"

Substitute the name of your sheet (the sheet in the
Location_Summary_July09.xls file) in place of Otto. Retain the quotes.

I recommend that you place 2 buttons at the top of that sheet, labeled
something like "Batch Processing" and "Interactive Processing" and assign
the appropriate macros to those buttons.

I made up some dummy files and ran this code and it appears to work as
you
want. Make sure you view this post in full screen before you copy the
code. This is to avoid line wrapping in the code. HTH Otto

Option Explicit
Dim ThePath As String, TheFile As String
Dim rColA As Range, First As Range, Last As Range
Dim Dest As Range, c As Long, wb As Workbook
Dim bFileExists As Boolean, rsFullPath As String
Const TheSht As String = "Otto"

Sub BatchProcessing()
Application.ScreenUpdating = False
Set wb = ThisWorkbook
ThePath = ThisWorkbook.Path
If IsEmpty(Range("A3").Value) Then
Set Dest = Range("A3")
Set rColA = Dest
Else
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
End If
ChDir ThePath
TheFile = Dir("*.xls")
Do While TheFile < ""
If Left(TheFile, 16) < "Location_Summary" Then
Workbooks.Open Filename:=ThePath & "\" & TheFile
With Sheets("Database")
If Not rColA.Find(What:=.Range("A2").Value) Is Nothing
Then
Set First = rColA.Find(What:=.Range("A2"),
After:=rColA(rColA.Count))
For c = 1 To 10000
If First.Offset(c).Value < First.Value Then
Set Last = First.Offset(c - 1)
wb.Sheets(TheSht).Range(First,
Last).EntireRow.Delete
Exit For
End If
Next c
End If
.Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(,
9).Copy Dest
With wb.Sheets(TheSht)
Set Dest = .Range("A" &
Rows.Count).End(xlUp).Offset(1)
Set rColA = .Range("A3", .Range("A" &
Rows.Count).End(xlUp))
End With
End With
ActiveWorkbook.Close
End If
TheFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

Sub InteractiveProcessing()
Application.ScreenUpdating = False
Set wb = ThisWorkbook
ThePath = ThisWorkbook.Path
If IsEmpty(Range("A3").Value) Then
Set Dest = Range("A3")
Set rColA = Dest
Else
Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColA = Range("A3", Range("A" & Rows.Count).End(xlUp))
End If
TheFile = InputBox("Enter the name of the workbook from which to
copy."
& Chr(13) & _
"The format must be 'FileName.xls'.")
If TheFile = "" Then
MsgBox "This program has terminated.", 16, "No Entry"
Exit Sub
End If
bFileExists = True
rsFullPath = ThePath & "\" & TheFile
bFileExists = Len(Dir$(rsFullPath))
If bFileExists = False Then
MsgBox "The file " & TheFile & " does not exist in the" & Chr(13)
&
_
ThePath & " folder." & Chr(13) & _
"This program will terminate.", 16, "Entry Error"
Exit Sub
End If
Workbooks.Open Filename:=ThePath & "\" & TheFile
With Sheets("Database")
If Not rColA.Find(What:=.Range("A2").Value) Is Nothing Then
Set First = rColA.Find(What:=.Range("A2").Value)
For c = 1 To 10000
If First.Offset(c) < First Then
Set Last = First.Offset(c - 1)
wb.Sheets(TheSht).Range(First, Last).EntireRow.Delete
Exit For
End If
Next c
End If
.Range("A2", Range("A" & Rows.Count).End(xlUp)).Resize(, 9).Copy
Dest
With wb.Sheets(TheSht)
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
Set rColA = .Range("A3", .Range("A" & Rows.Count).End(xlUp))
End With
End With
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub



"Ananth" wrote in message
...
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