Match and copy paste cells, from one sheet to another

[Nando1988]

I currently have 3 sheets:
Data1, Referencia1, and Summary
I am trying to get the column that's in column 1 and 2 of the sheet Referencia1, store it in a variable and use it to copy paste the columns in the order that is specified in columns 1 and 2.
In column A of Referencia1 sheet, I have the columns as letters, and in column b I have the columns as numbers. What I need to do is iterate through all the rows in Referencia 1, that are like 500, and then copy the column specified in column A of Referencia1 and paste it in the column specified in column B of Referencia1 into sheet Summary in the column specified in column B of Referencia1.

This is my current code though it gives em an error: Application defined or object defined error

Sub AZURESummary()
For cel = 1 To 500
col1 = Worksheets("Referencia1").Cells(cel, 1)
col2 = Worksheets("Referencia1").Cells(cel, 2)
        If Sheets("Referencia1").Cells(cel, col2).Value = 44 Then
        'AX
            If Sheets("Data1").Cells(1, col1).Value <> "" And Sheets("Data1").Cells(1, col1).Value <> 0 Then
                Sheets("Summary").Cells((cel + col1), col2).Value = "Si"
            End If
        End If
        If Sheets("Referencia1").Cells(cel, col2).Value = 287 Then
        'JL
            If Sheets("Data1").Cells(1, col1).Value <> "" And Sheets("Data1").Cells(1, col1).Value <> "No aplica porque no es necesaria" Then
                Sheets("Summary").Cells((cel + col1), col2).Value = "Si"
            End If
        End If
        If Sheets("Referencia1").Cells(cel, col2).Value = 227 Then
        'HA
            If Sheets("Data1").Cells((cel + 1), col1).Value <> "" And Sheets("Data1").Cells((cel + 1), col1).Value <> 0 Then
                Sheets("Summary").Cells((cel + col1), col2).Value = "Si"
            End If
        End If
        If col2 <> 44 And col2 <> 227 And col2 <> 287 Then
        End If

        If Sheets("Referencia1").Cells(cel, 1).Value <> "" Then
            'str A
            Sheets("Data1").Cells(1, col1).EntireColumn.Copy
            Sheets("Summary").Cells(1, col2).PasteSpecial
        End If
Next cel
End Sub
'Application defined or object defined error

 

[tico_ocit]

Try this... What I think is happening is that col1 and col2 eventually get "empty" values, as so the line Sheets("Referencia1").Cells(cel, col2).Value, can't be found.

Sub AZURESummary()

lastcol1_row = Cells(Rows.count, "A").End(xlUp).Row
'USE THE FOLLOW LINE IF THE NUMBER OF ROWS ON COLUMN A IS DIFFERENT FROM B
'lastcol2_row = Cells(Rows.count, "B").End(xlUp).Row
Cells(Rows.count, "A").End(xlUp).Row
For cel = 1 To lastcol1_row
col1 = Worksheets("Referencia1").Cells(cel, 1)
col2 = Worksheets("Referencia1").Cells(cel, 2)
        If Sheets("Referencia1").Cells(cel, col2).Value = 44 Then
        'AX
            If Sheets("Data1").Cells(1, col1).Value <> "" And Sheets("Data1").Cells(1, col1).Value <> 0 Then
                Sheets("Summary").Cells((cel + col1), col2).Value = "Si"
            End If
        End If
        If Sheets("Referencia1").Cells(cel, col2).Value = 287 Then
        'JL
            If Sheets("Data1").Cells(1, col1).Value <> "" And Sheets("Data1").Cells(1, col1).Value <> "No aplica porque no es necesaria" Then
                Sheets("Summary").Cells((cel + col1), col2).Value = "Si"
            End If
        End If
        If Sheets("Referencia1").Cells(cel, col2).Value = 227 Then
        'HA
            If Sheets("Data1").Cells((cel + 1), col1).Value <> "" And Sheets("Data1").Cells((cel + 1), col1).Value <> 0 Then
                Sheets("Summary").Cells((cel + col1), col2).Value = "Si"
            End If
        End If
        If col2 <> 44 And col2 <> 227 And col2 <> 287 Then
        End If

        If Sheets("Referencia1").Cells(cel, 1).Value <> "" Then
            'str A
            Sheets("Data1").Cells(1, col1).EntireColumn.Copy
            Sheets("Summary").Cells(1, col2).PasteSpecial
        End If
Next cel
End Sub
'Application defined or object defined error