検索と特定の条件に基づいて外部のワークブックから値を取得し、Microsoft ExcelでVBAを使用してフォーマットスタイルと一緒に値を貼り付けます。

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