Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile data from several workbooks | Excel Worksheet Functions | |||
Copy/ move selected data from workbooks to seperate worksheets or workbooks | Excel Worksheet Functions | |||
Automated task to present data in a graph form. | Charts and Charting in Excel | |||
Automated task to present data in a graph form. | Charts and Charting in Excel | |||
Using a user form in various workbooks | Excel Programming |