Macro for Match/ Index then Copy Paste

[MJA001]

Hello,

I'm new to the forum and to Macro writing.

I'm looking for a macro to help me copy and paste city names from column A in sheet2 into sheet1 column C if the names from sheet2 column C match the name in sheet1 cell B1.

Then move on through all the rows of column C in sheet2 until it hits an empty cell and can stop.


Can someone help me out? From my limited understanding I believe this has something to do with Index Match but I'm having trouble figuring out where to start.

Thanks in advance.

If this topic has already been addressed, please point me to thread.

 

[Trebor76]

Hi MJA001,

Welcome to MrExcel!!

See how this goes:

Option Explicit
Option Compare Text 'Make case matching insensitive
Sub Macro1()

    Dim rngMyCell As Range
    
    Application.ScreenUpdating = False

    For Each rngMyCell In Sheets("Sheet2").Range("C2", Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp)) 'Works from cell C2 down column C in Sheet2. Change to suit if necessary.
        If rngMyCell = Sheets("Sheet1").Range("B1") Then
            Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = rngMyCell.Offset(0, -2)
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[MJA001]

Thanks Robert! I'll give it a try and let you know how it goes.

 

[MJA001]

You're the best Robert! This worked perfectly.

 

[Trebor76]

Glad to hear it