Searching and getting value from external workbook based on certain condition and pasting value along with format style using VBA in Microsoft Excel.

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.

Logic explanation

“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:-

1. Using procedure

2. Using worksheet change event

Using procedure

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:-

1. Click on sheet name in the Visual Basic Editor, for activating the sheet module.

1. Click on the worksheet in the left combo box on the top of code window.

1. 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

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

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 info@exceltip.com