Copy Data from Row into certain Cells on another sheet than. LOOP through each row

[baker_89]

I want to copy data from a table and paste into certain cells on another sheet in a certain order than loop through each row. (This is to print out data from the table in a format with data from each row).

Source Data is a table B5:M500 (Row 4 is the Table Headers names as such below) (Named "Table1")

[TABLE="width: 1621"]
<tbody>[TR]
[TD="class: xl66, width: 95"]Work Order[/TD]
[TD="class: xl67, width: 89"]Assigned[/TD]
[TD="class: xl66, width: 457"]Description[/TD]
[TD="class: xl66, width: 195"]Charge To Name[/TD]
[TD="class: xl66, width: 89"]Charge To[/TD]
[TD="class: xl68, width: 110"]Custom Status[/TD]
[TD="class: xl70, width: 95"]Status[/TD]
[TD="class: xl66, width: 69"]Type[/TD]
[TD="class: xl66, width: 88"]Created[/TD]
[TD="class: xl66, width: 104"]Scheduled[/TD]
[TD="class: xl66, width: 85"]Priority[/TD]
[TD="class: xl63, width: 145"]Creator[/TD]
[/TR]
</tbody>[/TABLE]


Destination is a Sheet names "Print Out"

I need,

Column B to H3
Column C to A6
Column D to A12
Column E to A9
Column I to F6
Column J to F3
Column M to C3

For each row in the same column to the destination sheet than I print each sheet, loop to the next row with data and continue until there is no more data in the rows down the table. This is what I have so far, I can do one cell at a time but not sure how to get more than one cell at time before each Print. I have just been combining all relevant data into one cell but I would like to seperate it individually.






Sub Picture1_Click()
'Print Out All Parts Search Work Orders


Dim cell As Range

Msg = "Are you sure you want to print out ALL WORK ORDERS that require a parts search?"

Ans = MsgBox(Msg, vbYesNo)

Select Case Ans

Case vbYes

Application.DisplayAlerts = False

'Rotate through all cells and paste data into the Print Out Sheet than repeat with next line
For Each cell In Worksheets("PRINTLIST").Range("G2", Worksheets("PRINTLIST").Range("G" & Rows.Count).End(xlUp))

Worksheets("Print Out").Range("A11").Value = cell.Value


ActiveSheet.PrintOut


Next cell

Application.DisplayAlerts = True

Case vbNo
GoTo Quit:
End Select

Quit:


End Sub


PRINTLIST is just a helper sheet that separates data from Table1 by what is within the custom status row (i.e. labeled by priority 1-4 or some other text condition)

 

[Rosen]

Assuming I am understanding the situation here is some code to help:

Sub Picture1_Click()
'Print Out All Parts Search Work Orders
    Dim cell As Range
    Dim msg As String
    Dim ans As VbMsgBoxResult
    Dim lastRow As Long
    Dim Sheet As Worksheet
    Dim n As Long
    
    msg = "Are you sure you want to print out ALL WORK ORDERS that require a parts search?"
    ans = MsgBox(msg, vbYesNo)
    Select Case ans
        Case vbYes
        
            Application.DisplayAlerts = False
            Set Sheet = Worksheets("PRINTLIST")
            lastRow = Sheet.Cells(Sheet.Rows.Count, "B").End(xlUp).row
            For n = 5 To lastRow Step 1
                With Worksheets("Print Out")
                    .Range("H3").Value = Sheet.Range("B" & n).Value
                    .Range("A6").Value = Sheet.Range("C" & n).Value
                    .Range("A12").Value = Sheet.Range("D" & n).Value
                    .Range("A9").Value = Sheet.Range("E" & n).Value
                    .Range("F6").Value = Sheet.Range("I" & n).Value
                    .Range("F3").Value = Sheet.Range("J" & n).Value
                    .Range("C3").Value = Sheet.Range("M" & n).Value
                    .PrintOut
                End With
            Next
            Application.DisplayAlerts = True
        Case vbNo
            GoTo Quit
    End Select
Quit:
End Sub

Just remember to backup your work before running new code.

Hope this helps!