Paste data to another column if the value matches

Question

I have wrote macro for fun (I have just started learning VBA) to loop through a list of names in a column in sheet1, and if the name matches to a similliar list in sheet2, then paste the rest of the data in sheet2. But it keeps me giving an application error, although I have checked my code a countless of time I am pretty sure is some dumb mistake but I am unable to find it.

Option Explicit


Sub RangePasteColumn()
Dim j As Long, i As Long, lastRow1 As Long, lastRow2 As Long
Dim MyName As String

Sheets("sheet1").Activate
lastRow1 = Sheets("sheet1").Range("E" & Rows.Count).End(xlUp).Row

For j = 4 To lastRow1
MyName = Sheets("sheet1").Cells(j, "E").Value

Sheets("sheet3").Activate
lastRow2 = Sheets("sheet3").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow2
    If Sheets("sheet3").Cells(i, "A").Value = MyName Then
    Sheets("sheet1").Activate
    Sheets("sheet1").Range(Cells(j, "F"), Cells(j, "I")).Copy
    Sheets("sheet3").Activate
    Sheets("sheet3").Range(Cells(i, "B"), Cells(i, "E")).Select
    ActiveSheet.Paste
End If

Next i
Application.CutCopyMode = False

Next j
Sheets("sheet3").Activate
Sheets("sheet3").Range("A1").Select

End Sub

I know you can do a simple vlookup or index/match function for this task, I was just doing for a sake of learning not for work. Hope u guys can guide me here.

yea, one more thing, I was wondering if i can use the offset in my vba code, rather then writing which range to copy. if you know please let me know.

thanks

[How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, May 31 '14 at 08:06
+1 for "I wrote a macro for fun" Check simoco's link and the answer below, these are two good places to start. Yes, there is `Range.Offset()` that you can use, but in most cases there are better alternatives (again, simoco's link). Also, I avoid sheet names, because when someone changes them the code fails. Here are two better alternatives: http://www.ozgrid.com/VBA/excel-vba-sheet-names.htm Have fun! :) — Ioannis, May 31 '14 at 13:01

score 1 · Answer 1 · answered May 31 '14 at 12:52

Hello and welcome to StackOverflow.

This is a solution I came up with. Hopefully will give you some insight on how to keep working to streamline your code and avoid irrelevant coding. Let me know if it works for you. Please save a copy before running the macro (as you should always).

Regards,

  Option Explicit
Sub RangePasteColumn()
Dim j As Long, i As Long, lastRow1 As Long, lastRow2 As Long
Dim sh_1, sh_3 As Worksheet 'Dim for the worksheet objects we will create below
Dim MyName As String

Set sh_1 = Sheets("sheet1") 'These objects avoid you having to write Sheets("SheetX") multiple times
Set sh_3 = Sheets("sheet3")

'Sheets("sheet1").Activate - There is no need to use Activate/Select a range or sheet. You can work on them by accessing
'the values directly
lastRow1 = sh_1.UsedRange.Rows.Count 'This is a better function to get the last used row (though there are disagreements on this)

For j = 4 To lastRow1
MyName = sh_1.Cells(j, 5).Value 'Column E = 5

'Sheets("sheet3").Activate - Again no need to use Activate a sheet
lastRow2 = sh_3.UsedRange.Rows.Count

    For i = 2 To lastRow2
        If sh_3.Cells(i, 1).Value = MyName Then 'Column A =1
            'Sheets("sheet1").Activate - I think you understood already :P
            sh_3.Cells(i, 2).Value = sh_1.Cells(j, 6).Value 'This is much better, faster way to "copy and paste" values
            sh_3.Cells(i, 3).Value = sh_1.Cells(j, 7).Value
            sh_3.Cells(i, 4).Value = sh_1.Cells(j, 8).Value
            sh_3.Cells(i, 5).Value = sh_1.Cells(j, 9).Value
            'Sheets("sheet3").Activate - Hopefully you did!
            'Sheets("sheet3").Range(Cells(i, "B"), Cells(i, "E")).Select
            'ActiveSheet.Paste
        End If

    Next i

Next j
'Sheets("sheet3").Activate - You most definitely did
'Sheets("sheet3").Range("A1").Select - Yeah! no need to use select either
MsgBox "Process Finished!"
End Sub

Hey Giusepe, thank you very much the code worked nicely. I especially liked the way you referenced to the sheets instead of activating them every time, saves a lot of time and makes ur code look more easy to understand. thanks again — user2722393, Jun 01 '14 at 15:04

score 0 · Answer 2 · answered May 31 '14 at 15:44

I'd try something like this. Define your ranges upfront and then iterate over each and use .Copy Destination:= construction to move data across.

Sub RangePasteColumn()
    Dim names As Range, name As Range, values As Range, value As Range

    Set names = Worksheets("Sheet1").Range("E4:E" & Worksheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row)
    Set values = Worksheets("Sheet3").Range("A2:A" & Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row)

    For Each name In names
        For Each value In values
            If name.value = value.value Then
                Worksheets("Sheet1").Range("F" & name.Row & ":I" & name.Row).Copy Destination:=Worksheets("Sheet3").Range("B" & value.Row & ":E" & value.Row)
            End If
        Next value
    Next name
End Sub

Hey Alex thanks alot your answer and the answer below have really provided me with different perspective on the VBA. your code makes look VBA so simple. thanks — user2722393, Jun 01 '14 at 15:06

Paste data to another column if the value matches

2 Answers2