Excel Macro VBA Copy Paste Between Sheets

[Melpp]

Hi! I am a new user with Excel Macros and VBA. I need to copy some data from one main worksheet into individual worksheets based on the value set in one of the columns. Here is the code I am currently using. The code does not run and stops at the line "Worksheets("Sheet 1").Range("Cells(i,1)").Offset(0, 7).Selection.Copy" Please help me.

Worksheets.Add().Name = " Anterior"

For i = 5 To Last_Row + 3
If Worksheets("Sheet 1").Cells(i, 2).Value = "Anterior" Then
Worksheets("Sheet 1").Range("Cells(i,1)").Offset(0, 7).Selection.Copy
Worksheets("Anterior").Range("Cells(i,2)").Paste

End If
Next i

 

[absetiawan]

Hi Melpp.

As Peter shown you, I think the main problem is you created sheet with name " Anterior" (with one leading space), and you try to copy it to sheet with name "Anterior" (with no leading space).

to make it more clearer, you can change the sheet name to other name (as anterior might confuse with data in cell(i,2).

best regards,
AnuG

 

[Melpp]

Hey, I found my mistake. Now I want to copy an entire row (or a few cells) rather than a single cell. How would I go about that?

 

[VoG]

Maybe like this

For i = 5 To Last_Row + 3
    If Worksheets("Sheet1").Cells(i, 2).Value = "Anterior" Then
        Worksheets("Sheet1").Cells(i, 1).Resize(0, 7).Copy Destination:=Worksheets(" Anterior").Cells(i, 2)
    End If
Next i