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

baker_89

New Member
Joined
Aug 25, 2014
Messages
42
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")

Work OrderAssignedDescriptionCharge To NameCharge ToCustom StatusStatusTypeCreatedScheduledPriorityCreator

<tbody>
</tbody>


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)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Assuming I am understanding the situation here is some code to help:
Code:
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top