» Search value in external workbook and paste with format style using VBA in Microsoft Excel
CATEGORY - Files, Workbook, and Worksheets in VBA
VERSION - All Microsoft Excel Versions
When entering a value in column B, value should be searched in a different workbook in row 1 the value in column A should be searched in column A of the second
Answer:
Insert the following code in the appropriate modules.
'Place the code below into the This Workbook module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Call ReadFormatting(Target.Offset(0, -1))
End Sub
'Place the code below into the standard module
Sub ReadFormatting(rng As Range)
Dim varRow As Variant, varCol As Variant
Application.ScreenUpdating = False
Workbooks.Open "c:temptest.xls", False
varRow = Application.Match(rng.Value, Columns(1), 0)
varCol = Application.Match(rng.Offset(0, 1).Value, Rows(1), 0)
If Not IsError(varRow) And Not IsError(varCol) Then
Cells(varRow, varCol).Copy
rng.Offset(0, 2).PasteSpecial xlPasteFormats
' The value will carry from:
'' rng.Offset(0, 2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
ActiveWorkbook.Close savechanges:=False
Application.ScreenUpdating = True
End Sub
Book Store:
Recommended Books:
- The One Page Business Plan: Start With a Vision, Build a Company!
- VBA for Modelers: Developing Decision Support Systems Using Microsoft« Excel
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Real Estate Finance and Investments (Real Estate Finance and Investments, 11th Ed)
- Infectious Greed: How Deceit and Risk Corrupted the Financial Markets
- Flipping Properties: Generate Instant Cash Profits in Real Estate
No comments have been submitted.

