Duplicating Data on a different sheet

[GraemeC]

Hi,

I have a macro question. I have 10 rows, and in column A I insert the data A1-A10 in each column consecutively. Then in column B I put data at random, e.g B12, B3, B144, B1, B3, B45 etc. If I run a macro in the same workbook but on a different sheet I can get column B to repeat the order exactly. But if I muddle the A column in sheet 3, so in A1 I write A5, in A2 I write A1 etc, the B column repeats the information from before.

What I want it to do is to read what’s written in column A and respond accordingly – e.g if in sheet 1 the data A1 has the data B12 next to it in the B column, then wherever the data A1 appears on sheet 3, the data B12 will appear next to it. Does this make sense? And can you help? I have attached a relevant spreadsheet.

Thanks,

Graeme

[MatrixMan]

Hi Graeme ... try this:

Sub GetMatchingData()

Dim intRows1 As Integer
Dim intRows2 As Integer
Dim i As Integer
Dim j As Integer
Dim strToFind As String
Dim strToWrite As String
Dim strMatch As String

    intRows1 = Worksheets("Sheet1").Range("A1").End(xlDown).Row
    intRows2 = Worksheets("Sheet2").Range("A1").End(xlDown).Row
    If intRows2 = 65536 Or Worksheets("Sheet2").Range("A1") = Empty Then GoTo ErrorExit
    
    For i = 1 To intRows1
        strToFind = Worksheets("Sheet1").Range("A" & i).Text
        strToWrite = Worksheets("Sheet1").Range("B" & i).Text
        For j = 1 To intRows2
            strMatch = Worksheets("Sheet2").Range("A" & j).Text
            If strToFind = strMatch Then
                Worksheets("Sheet2").Range("B" & j).Value = strToWrite
                GoTo GetNextLookup
            End If
        Next j
GetNextLookup:
    Next i
    
ErrorExit:

End Sub

Note that this relies on consecutive data with no blank cells in col A; if you need something more robust, then use specialcells instead of Range("A1").End(xlDown).

Hope that helps. MM

[GraemeC]

Thanks Matrix Man, I'll give it a go.