compare and compile nonblank data from seperate workbooks in an automated user form
 

Hello,
I am trying to simplify compiling of data from different users. Here
is the situation: A single master sheet is sent out to 5 different
regions. In this master sheet are hundreds and sometimes thousands of
shipping lanes. To simplify these lanes have an predetermined id
number, an origin, and a destination and a blank origin charge and
destination charge. If either the origin or destination resides in
that region, they fill in the appropriate charge and send back the
file.

The problem is I am receiving back 5 different sheets with scattered
information and manually copy pasting nonblank information from each
to create a complete master sheet. This has become time consuming so
using my vba knowledge (limited) I created a blank excel worksheet
with a form on it that has 5 buttons each labeled for a region, that
brings an open workbook dialog. Once a user selects the appropriate
workbook, the full path is stored in a cell next to the button.
Basically the user can select the 5 sheets (same worksheet, different
data) and prepare for them to be compiled.

Here is the code for each button.

Sub AMNO()
Dim amno_var As Variant
amno_var = Application.GetOpenFilename("Excel (*.xls),*.xls", ,
"Open dialog test")
If amno_var < False Then
Range("amno_txt") = amno_var
Else
MsgBox "the Open was cancelled"
End If

End Sub
Sub AMLA()
Dim amla_var As Variant
amla_var = Application.GetOpenFilename("Excel (*.xls),*.xls", ,
"Open dialog test")
If amla_var < False Then
Range("amla_txt") = amla_var
Else
MsgBox "the Open was cancelled"
End If

End Sub
Sub ASPA()
Dim aspa_var As Variant
aspa_var = Application.GetOpenFilename("Excel (*.xls),*.xls", ,
"Open dialog test")
If aspa_var < False Then
Range("aspa_txt") = aspa_var
Else
MsgBox "the Open was cancelled"
End If

End Sub
Sub EURO()
Dim euro_var As Variant
euro_var = Application.GetOpenFilename("Excel (*.xls),*.xls", ,
"Open dialog test")
If euro_var < False Then
Range("euro_txt") = euro_var
Else
MsgBox "the Open was cancelled"
End If

End Sub
Sub EMA()
Dim ema_var As Variant
ema_var = Application.GetOpenFilename("Excel (*.xls),*.xls", ,
"Open dialog test")
If ema_var < False Then
Range("ema_txt") = ema_var
Else
MsgBox "the Open was cancelled"
End If

End Sub

Those are the buttons, under which I have another button labeled
"compile", which is what I am trying to code.

Now the tricky part for me is to successfully create a new workbook
that compares and compiles the data. I suppose the first workbook to
open (AMNO) would simply copy to a new workbook. The second workbook
to open (AMLA) would have a loop that compared the ID numbers on each
sheet and if nonblank data within that row existed, it would copy that
nonblank data to its comparable cell within the new workbook. So on
and so forth. This is essentially a fill in the blank as each of the
5 sheets are a piece to the puzzle.

Any insight would be very appreciated, at this point im stuck!!

Thanks in advance.

compare and compile nonblank data from seperate workbooks in an au
 

Assuming this is not a situation where you can simply share the master sheet,
I can think of one other possibility. Export the data from each returned
sheet to a text file, using FileSaveAs. Read each file from a macro
function, integrating the data into your master sheet.

" wrote:

Hello,
I am trying to simplify compiling of data from different users. Here
is the situation: A single master sheet is sent out to 5 different
regions. In this master sheet are hundreds and sometimes thousands of
shipping lanes. To simplify these lanes have an predetermined id
number, an origin, and a destination and a blank origin charge and
destination charge. If either the origin or destination resides in
that region, they fill in the appropriate charge and send back the
file.