In this article, we will learn how to get data from external workbook in the active workbook based on certain defined condition.
In this example, we want to get money details for a particular person based on the name and type of bank category. Money details is stored in the “Data file” workbook.
In this example, we have written VBA procedure “ReadFormatting” which returns the money details along with format style.
“ReadFormatting” procedure takes range object as input and returns the output in the cell next to defined range object.
It checks the defined value in the range within the first row of “Data file” workbook and find the column number. It checks the value of the cell of previous column of defined range within the first column of “Data file” workbook and find the row number.
After finding the column number and row number of possible match, value of cell with found column number and row number is returned along with format style.
“ReadFormatting” procedure can be run by using other procedure or event.
We will run “ReadFormatting” procedure in two ways:-
Using worksheet change event
We have used “CallingProcedure” procedure for calling “ReadFormatting” procedure with cell N13 as range object. It will check the value in cell N13 in the first row of external workbook for finding the column number and check the value in cell M13 in the first column of external workbook for finding the row number. After finding the column number and row number, it will return the value along with the format style.
Using worksheet change event
For adding worksheet change event, follow the following steps:-
Click on sheet name in the Visual Basic Editor, for activating the sheet module.
Click on the worksheet in the left combo box on the top of code window.
Click on the change in the right combo box on the top of code window.
We have used worksheet change event for running the procedure. Worksheet change event is fired when the value of any cell in the workbook is changed. Using IF statement, we have restricted change event to trigger only when value in cell of the column I is changed. Cell whose value is changed, acts as input for “ReadFormatting” procedure.
Please follow below for the code
Option Explicit Sub CallingProcedure() 'Calling procedure ReadFormatting for cell N13 Call ReadFormatting(Range("N13")) End Sub Sub ReadFormatting(rng As Range) Dim varRow, varCol As Long Application.ScreenUpdating = False 'Activating workbook "Data file.xlsx" Workbooks("Data file.xlsx").Activate 'Checking for Errors 'If any runtime error occur then it will the pointer to end of the procedure On Error GoTo Last 'Finding the column no after matching rng value in the first row of "Data file.xlsx" workbook varRow = Application.Match(rng.Value, Rows(1), 0) 'Offset method is used for moving one cell in the previous column 'Finding the row no after matching value of cell in the first column of "Data file.xlsx" workbook varCol = Application.Match(rng.Offset(0, -1).Value, Columns(1), 0) 'Using If statement for checking errors 'If error not found in varRow and varCol then execute below code If Not IsError(varRow) And Not IsError(varCol) Then 'Copying value of cell where match of row and column intersect Cells(varCol, varRow).Copy 'Pasting the format of copied cell rng.Offset(0, 1).PasteSpecial xlPasteFormats 'Pasting the value of copied cell rng.Offset(0, 1).PasteSpecial xlPasteValues 'Unselecting the previous copied data and clearing the cache Application.CutCopyMode = False End If Application.ScreenUpdating = True Last: Workbooks("Searching_And_Getting_Data_From_Other_File_Along_With_Formatting.xlsm").Activate End Sub
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org