Auto print on 10 rows entry complete

[appu_gusai]

Hello
I have two sheets in Excel. One sheet has approx.2000 entries and the other sheet has 10 rows table. Now I want to insert data of 10 rows from sheet one to other sheet and as soon as 10 entries completed in first sheet the second sheet table should be printed automatically...and once print has been done the 11 to 20 entries from sheet one should be inserted in sheet two in the same table..and get it printed...and so on.... I want to use the same table for all the prints.... Entries are not done in sheet one..it will be made periodically... any help will be greatlfull.thanks..

 

[AlanSidman]

When you transfer the second 10 records will it replace or add to the first 10 records? Will the print after the second transfer be 10 or 20 records?

 

[appu_gusai]

Thanks for the reply
The table in second sheet will have only 10 rows..So first 10 records will be replaced by second 10 records.the print will be of 10 records only..As 1 to 10. ...11 to 20...21 to 30..And so on
Thanks...

 

[AlanSidman]

how about this: Note that I set up the print columns as A:D. Change this in the code as needed. Also, I assumed there were not headers so you may need to amend the code to reflect that since you failed to provide details on the layout of your workbook.

Option Explicit

Sub OnlyTen()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim i As Long, lr As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        s1.Range("A" & i & ":D" & i + 9).Copy s2.Range("A1")
        With s2
            .Range("A1:D10").Select
            Application.CutCopyMode = False
            ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
                                                IgnorePrintAreas:=False
            .Range("A1:D10").ClearContents
        End With
        'MsgBox "Stepped"
        i = i + 9
        Application.CutCopyMode = False
    Next i

End Sub

 

[appu_gusai]

Thanks for the reply Alan..
will implement the code and will let you know ...
Thank you very much....