Need to edit macro to copy ONLY the 1st three cells--not entire row, please help

[amandabstewart]

I've been trying (for HOURS) to achieve the following goal:

Need to take the first three cells of rows and copy them to a different worksheet when the word "Alex" appears in column B.

I've tried editing the following macro that copies the whole row but nothing I change the entire row.copy command to works

I'm trying to copy Alex's work info from my Work Log worksheet to his individual worksheet without writing over the contents to the right of where the copied cells will go.

If I can get this to work, I had hoped I could expand the macro to do the same for other names as well (eg copy Timmy's work to his worksheet, etc.)




Sub MoveWork()
Dim x As Long, y As Long
y = 1
Application.ScreenUpdating = False

For x = 1 To Worksheets("Work Log").Range("B65536").End(xlUp).Row
If InStr(1, Worksheets("Work Log").Range("B" & x), "Alex") > 0 Then
Worksheets("Work Log").Range("B" & x).EntireRow.Copy
Worksheets("Alex").Range("A" & y).PasteSpecial
Application.CutCopyMode = False
y = y + 1
End If
Next x
Application.ScreenUpdating = True
End Sub

 

[Michael M]

Maybe this

Sub MoveWork()
Dim x As Long, y As Long, ws As Worksheet
Application.ScreenUpdating = False
Set ws = Worksheets("Work Log")
y = 1
    For x = 1 To ws.Cells(Rows.Count, "B").End(xlUp).Row
        If InStr(1, ws.Range("B" & x), "Alex") > 0 Then
            ws.Range("A" & x & ":C" & x).Copy
            Worksheets("Alex").Range("A" & y).PasteSpecial
            Application.CutCopyMode = False
            y = y + 1
        End If
    Next x
Application.ScreenUpdating = True
End Sub

 

[amandabstewart]

That worked partially.In the Work Log worksheet it copied the first and last matches but missed an “Alex” record in the middle of the list.

Also, I tried editing the macro to begin entering the copied material in A28 on the Alex worksheet rather than A1 with no luck : (

It is also most helpful if I take column A, C, and D info from the worklog rather than the first 3

Suggestions?
THANKS so much in advance!I’m roughly familiar with basic macros…trying very hard to get the swing of things (and thus become less of a pain in the rear on this forum!)

 

[amandabstewart]

A friend stopped by (who is a programmer but not excel guy) and we came up with the macro below. I'm sure there is a one-line correction, but I thought I would share anyway

Sub MoveWork()
Dim x As Long, y As Long, ws As Worksheet
Application.ScreenUpdating = False
Set ws = Worksheets("Work Log")
y = 34
For x = 1 To ws.Cells(Rows.Count, "B").End(xlUp).Row
If InStr(1, ws.Range("B" & x), "Alex") > 0 Then
ws.Range("A" & x).Copy
Worksheets("Alex").Range("A" & y).PasteSpecial
ws.Range("C" & x & ":D" & x).Copy
Worksheets("Alex").Range("B" & y).PasteSpecial
Application.CutCopyMode = False
y = y + 1
End If
Next x