<VBA> Looping and Copying

[ShadowRecon10]

Hello All,

I've been trying to figure out how to write a VBA macro to loop through the cells of a column, matching cell(s) with a criteria, copy a cell from that row to a different sheet, and then print a specified selection.

This table is located on Sheet "Data". I need to loop through Column C "Manager". When looping through C, I am looking for instances of "Thomas,Kelly". When an instance of "Thomas,Kelly" is found, I want to copy the User cell and paste it into Cell B6 on Sheet "Report". My Report sheet will update other data based on the User pasted into it, then once I do my selection print on the Report Sheet, I want the loop to continue back on the "Data" sheet until the next instance of "Thomas,Kelly" and do the same thing, copy the User cell to Report B6, do my update and print, and continue on until the search gets to the last row.

[TABLE="class: cms_table_grid, width: 100"]
<tbody>[TR]
[TD]User[/TD]
[TD]Name[/TD]
[TD]Manager[/TD]
[/TR]
[TR]
[TD]john1[/TD]
[TD]Herbert,John[/TD]
[TD]Thomas,Kelly[/TD]
[/TR]
[TR]
[TD]joejoe20[/TD]
[TD]Johnson,Josph[/TD]
[TD]Blanton,Joe[/TD]
[/TR]
[TR]
[TD]willy5[/TD]
[TD]Rose,Wilson[/TD]
[TD]Thomas,Kelly[/TD]
[/TR]
[TR]
[TD]ronny12[/TD]
[TD]Wilson,Ronny[/TD]
[TD]Melody,Sarah[/TD]
[/TR]
</tbody>[/TABLE]


Any and all help would be appreciated. I've been racking my brain on it for hours, but can't figure out how to do it.

Thank you in advance for the help.

 

[Scott T]

Try, for the printing you can start the macro recorder and then select the print options you want then stop the recorder and then replace the print line I have so that it print how you want it.

Sub copyandprint()
Dim lr As Long
Dim wsd As Worksheet
Dim wsr As Worksheet
Dim myfound As Boolean
Set wsd = Sheets("Data")
Set wsr = Sheets("Report")
myfound = False
lr = wsd.Cells(Rows.Count, 3).End(xlUp).Row
manager = InputBox("Enter manager name")
For x = 2 To lr
    If UCase(wsd.Cells(x, 3)) = UCase(manager) Then
        wsd.Cells(x, 1).Copy wsr.Range("B6")
        wsr.PrintOut 'Change to match the print criteria you want
        myfound = True
    End If
        
Next x
If myfound = False Then MsgBox "Manager not found"

End Sub

 

[ShadowRecon10]

Thank you.

I was able to add it to my coding and it does exactly what I need it to do. It looks so simple, can't believe that I couldn't figure that out. Guess I have a lot to learn/study.