This week I will demonstrate how to take data from multiple fields within different Excel documents and transfer that data from each spreadsheet into a distinct row of another spreadsheet which will act as a the centralized repository workbook.
Let’s say you have multiple forms similar to the one below and each form is stored within a separate spreadsheet. 
I have attached four such forms below (i.e. Forms A, B, C and D).
Each form contains the following fields: “Order Date”, “First Name”, “Last Name”, “Customer ID #”, and "Order ID #”. Our objective is to transfer these fields from the four forms into a row within another spreadsheet which is the centralized repository. Here is how this can be done using a Macro.
STEP 1] Create a new Macro enabled worksheet and insert the name of the five aforementioned fields, which need to be extracted, in the header row (i.e. in column A). 
STEP 2] Insert the macro code within the Word document below in the Macro section located under: “Developer”> “Visual Basics”> “Insert”> “Module”.
Note: the “textFile.save” command at the end of each subset of codes ensures that the result is saved so that a popup window does not appear for each spreadsheet prompting to save.
STEP 3] Run the Macro.
Note: make sure the four source files are closed prior to running the macro. 
The macro prompts me to select one or more files from your hard drive. I will select the four files.
Here is the desired outcome after successfully running the macro.
0 Comments
Your comment will be posted after it is approved.
Leave a Reply. |
|||||||||||||||||
