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

 

[njimack]

Welcome to the board.

You don't want to select the range you're copying. Try the below.

Worksheets.Add().Name = " Anterior"

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

 

[VoG]

Try

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

 

[Melpp]

Unfortunately, the code still does not run with either of those solutions. Any other advice?

 

[VoG]

Do you get an error? If so click Debug - which line of code is highlighted?

 

[njimack]

Are you sure that at least one of the cells in column B of sheet 1 contain the word "Anterior" (without any leading/trailing spaces)?

 

[Melpp]

The line following the 'If' statement

 

[VoG]

You need a leading space in the destination sheet name

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

 

[Melpp]

Thanks! It no longer says there is an error. The problem is that it doesnt actually copy the values/range into the new sheet. Not sure why. Any ideas?

 

[VoG]

Are you sure that column B contains exactly (and only) the word Anterior and that there is something to copy in column H?