Macro/VBA needed to find, match, copy/paste

 IMnotGoodATvba -

Ok so i've searched and searched and still haven't found code to do exactly what I need.
The macro needs to be able to search hundrends of cells in the same column (sheet1) and if a cell in that column matches the first cell in another worksheet (Sheet2), then the macro pastes the following cells (from the same row on sheet2) on sheet1 (same row but different column ).
So i'm basically trying to cut out of the work of matching text from two different worksheets and copying and pasting the related data into the same row.
I found this macro as a standard module (not sure how helpful it is, but could be a starting point).

Option Explicit
Sub CopyData()
Dim cell As Range
Dim rw As Long
For Each cell In Worksheets("PTR").Range("A:A").Cells
If cell <> "" Then
rw = Lookup(cell.Value)
If rw <> 0 Then
Worksheets("PTR").Cells(cell.Row, "L").Resize(, 4).Value = _
Worksheets("Reference").Cells(rw, "L").Resize(, 4).Value
End If
End If
End Sub
Function Lookup(item As String) As Long
On Error Resume Next
Lookup = WorksheetFunction.Match(item, Worksheets("Reference").Range("A:A"),
On Error GoTo 0
End Function

2 replies

Registration date
Saturday April 11, 2009
Last seen
June 21, 2011
you can do it without a macro
Put the below formula in B1 in sheet 2

The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

except that this will not work if your trying to bring over the formatting as well :)
ok I have a simillar problem.

what I want is a macro code that can match 2 colums in different sheet
if the information in sheet.2 could not be found in sheet.1

then write it in sheet.3

at the end we will have only the new information collected in sheet.3

is there any macro that can do this!?

Thanks guys

You will need to change the two column lines to the columns you want the Macro to search in and also the sheet names. So Change "ABCDEFG" to whatever the column name is and the number of columns from the left it is after the "=" Sign. I am working on a way to delete the rows that have been matched a copied to the master sheet. Thanks to "The Real Tin Tin" for all your help to the Excel community

Option Explicit

Private Const MASTER_ABCDEFG_COL_IDX As Integer = 7
Private Const DATA_ABCDEFG_COL_IDX As Integer = 5

Public Sub FindMatchingData()
Dim MasterWrkSh As Worksheet
Dim MasterColCount As Integer
Dim MasterRowCount As Long
Dim MasterCol As Integer
Dim MasterRow As Long
Dim DataWrkSh As Worksheet
Dim DataColCount As Integer
Dim DataRowCount As Long
Dim DataRow As Long
Dim CopyData As Integer
Dim PrintData As Integer
Dim Data() As Variant

' Prevent ScreenUpdating and Alerts to improve performance while running.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Cursor = xlWait

' We use objects to store the WorkSheets we'll be working on to make altering the _
code easier if required.
Set MasterWrkSh = Sheets("Master")
Set DataWrkSh = Sheets("Data")

' Store the Row/Column limits. This prevents us from continuously checking these, _
improving performance.
MasterColCount = MasterWrkSh.UsedRange.Columns.Count
MasterRowCount = MasterWrkSh.UsedRange.Rows.Count
DataColCount = DataWrkSh.UsedRange.Columns.Count
DataRowCount = DataWrkSh.UsedRange.Rows.Count

' Prepare the dynamic array for data. If this is not done, the code will fall over.
ReDim Data(DataColCount, 0)

' These nested For loops, check each row on the Master and Data WorkSheets and _
compare the PSN columns. Where a match is found, the data for the equivalent _
row on the Data WorkSheet is stored in the Data array, which we will use later _
copy the data over to the Master WorkSheet.
For MasterRow = 1 To MasterRowCount
' These 2 lines simply update the StatusBar so the user knows what stage the _
"query" is at and also free's the CPU, preventing crashes and freezes should _
the user wish to cancel the code. _
NOTE: The running of the code can be cancelled by pressing Ctrl + Pause/Break.
Application.StatusBar = Format(MasterRow / MasterRowCount, "0 %")
For DataRow = 1 To DataRowCount
' This is the If statement that checks whether we have a match.
If MasterWrkSh.Cells(MasterRow, MASTER_PSN_COL_IDX) = _
DataWrkSh.Cells(DataRow, DATA_PSN_COL_IDX) Then
ReDim Preserve Data(DataColCount, UBound(Data, 2) + 1)
Data(0, UBound(Data, 2)) = MasterRow
For CopyData = 1 To DataColCount
Data(CopyData, UBound(Data, 2)) = DataWrkSh.Cells(DataRow, CopyData)
' As we found a match, we can Exit the For loop as we don't need to check _
for further matches.
Exit For
End If

' Now we've compaired all the data we need to, these nested For loops will "paste" _
this data on the Master WorkSheet. _
NOTE: No StatusBar updates are provided during this process. If the process takes _
an excessive amount of time, it may be beneficial to change this.
For PrintData = 1 To UBound(Data, 2)
For MasterCol = 1 To UBound(Data, 1)
MasterWrkSh.Cells(Data(0, PrintData), MasterCol + MasterColCount) = Data(MasterCol, PrintData)

' Return full control to Excel and refresh the sheet.
Application.StatusBar = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Cursor = xlDefault

' Free up resources.
Set DataWrkSh = Nothing
Set MasterWrkSh = Nothing

End Sub