Copying the first 50 data from a list of 1500 to another sheet and to overwrite the 50 till i am don

[odartey]

Hello all
i hope you are all doing great.
i have a challenge and i need you help.
thanks
My Challenge;
i have a list of data starting from B2:b1500,holding the names of students.In another Sheet i have created a summary of which will pick the first 50 names from B2:B1500 and paste themin this summary sheet for onward bill generation for the pupils.
My challenge now is to have a macro or VB which will copy the first 50 and go paste in the summarry sheet and then goes back and copies the next 50 to overwrite the initial 50 in the summary and on and on it should go till i am done .......
Your help is much appreciated
odartey

 

[Hui]

Odartey

What about this simple bit of VBA?

Sheets("Sheet1").Select
For i = 1 To 29
  Range("B2:B51").Select
  Selection.Cut Sheets("Summary").Range("B2")
  Sheets("Sheet1").Select
  Selection.Delete Shift:=xlUp
Next i

 

[Deepak]

Sub CopyPaste()
Dim mylast As Long

With Sheet1
    mylast = .Range("AA11").Value
    If Not mylast > 1 Then mylast = 2
   
    If mylast > Application.CountA(.Columns(2)) Then Exit Sub
       
    .Cells(mylast, 2).Resize(50).Copy Sheets("Summary").[A2]
   
    .Range("AA11").Value = mylast + 50
End With

End Sub

 

[Luke M]

Simply curious, what does this accomplish? Why not just take the last 50 records? It seems like the macro is just going to overwrite the cells in Summary sheet multiple times, faster than you can do anything with it.

 

[odartey]

Actually what i want to do is to pick the first 50 into the summary sheet and then print, after printing go in for the next 50 to the summary and print....
this is what i want to achieve Luke M

 

[Luke M]

Actually what i want to do is to pick the first 50 into the summary sheet and then print, after printing go in for the next 50 to the summary and print....
this is what i want to achieve Luke M

Ah, that makes more sense. You had me deeply curious there.

 

[Deepak]

Ah, that makes more sense. You had me deeply curious there.

Hi odartey,

Why not you take pain to upload the sample file to get a nice answer.

 

[odartey]

Ok, Deepak
I have uploaded a sample file
thanks

 

[Nebu]

May be something like this

Sub printstudent()
Application.ScreenUpdating = False

Dim i, j, k, c, c1 As Long

i = Sheet5.Cells(Rows.Count, "B").End(xlUp).Row
k = Round((i / 50), 0) + 1
c = 51
c1 = 2

For j = 2 To k
    Application.CutCopyMode = False
    Sheet5.Range("B" & c1 & ":B" & c).Copy
    Sheet6.Range("B4").PasteSpecial xlValues
    Sheet6.Range("B4:B" & c).PrintOut
    c = c + 50
    c1 = c1 + 50
Next j

Application.ScreenUpdating = True
End Sub

This code will loop and print the Range("B4:B50") from the sheet PrintList.

Thanks

 

[odartey]

Thanks Nebu.
Its working fine, too fine.....
If i decide to change the print area to another sheet, i believe the PrintOut code will read like this;
Sheet3.Range("A1:A" & c).PrintOut
the reason for this is because i have set out invoices to be printed from that sheet with a print area spanning from A1:h1701.
Please i need your expert advice
Thanks once again

 

[Nebu]

Hi:

Yes you are right, but you can change the code

Sheet3.Range("A1:A" & c).PrintOut
to Sheet3.Range("A1:A50").PrintOut

Coz your print area will be static not dynamic. In the code I had given (Sheet6.Range("B4:B" & c).PrintOut), there is a logical error in using a dynamic range using "c" as an increment. If you can make this minor change you are good to go.

Thanks

 

[odartey]

Thanks Nebu,
Its working good but i am having a challenge tweaking it to print the reports the way i want, so i am uploading a sample data file so that you cross check the VB for me
the flow in the sample file is 1. first 50 to be copied to PRINTLIST, 2. then that 50 list feeds into BILL 3. then we print BILL
thanks, i am very appreciative of your help

 

[Nebu]

Hi:

some clarifications needed:

• How are you populating the tab Bill? Do the bill tab pre-populated before running the macro.
• Is it going to be static?
• Do you want each bill to be printed in separate sheets?(I would assume so)

Thanks

 

[odartey]

Hello,
this is it
1.the setup sheet holds the ID's and names as when a pupil is admitted
2.the PRINTLIST sheet will pick the first 50 which is linked to the BILL sheet to be populated for printing
50 because i have just created 50 bill sheets to be printed at any given time when the PRINTLIST is populated from the SETUP sheet
i hope this explains myself
thanks

 

[Nebu]

Hi:

Try the following code, as per my understanding you need to have only one template in the billing tab. The macro has to fill in the rest of the details and loop through all the 50 names and print 50 receipts separately. The given macro will fill in the name and the student ID and print( I am not sure about the other details to be filled in if you can specify that I can code that as well).

Sub printstudent()
Application.ScreenUpdating = False

Dim i, j, k, c, c1, n1, n As Long

i = Sheet5.Cells(Rows.Count, "B").End(xlUp).Row

k = Round((i / 50), 0) + 1
c = 51
c1 = 2

For j = 2 To k
    Application.CutCopyMode = False
    Sheet5.Range("B" & c1 & ":B" & c).Copy
    Sheet6.Range("B4").PasteSpecial xlValues
    n = Sheet6.Cells(Rows.Count, "B").End(xlUp).Row
        For n1 = 4 To n
            Sheet3.[C4] = Sheet6.Range("C" & n1)
            Sheet3.[D5] = Sheet6.Range("B" & n1)
            Sheet3.Range("A1:H34").PrintOut
        Next n1
    c = c + 50
    c1 = c1 + 50
Next j

Application.ScreenUpdating = True
End Sub

Thanks

 

[odartey]

Thanks Nebu
Works just as i wanted it.
you have eliminated my crude way of generating the bills
thanks once again, very much appreciated