• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy the 20 line item and paste in a format to print

abdulncr

Member
Dear Experts,

I am stuck with something, would be really helpful if a solution.

I have data in A2:L5000 in sheet1. in column 'I' it is 'Amount' and in Column 'D' it is 'Entry number'
In sheet2 I have format set with letter head which can accommodate 19 row.
I wanted to copy each 19 rows from sheet1 and paste Sheet2 one by one. data should break by Column "D".

Any help please

Thanks
Abdul
 

PCosta87

Well-Known Member
Dear Experts,

I am stuck with something, would be really helpful if a solution.

I have data in A2:L5000 in sheet1. in column 'I' it is 'Amount' and in Column 'D' it is 'Entry number'
In sheet2 I have format set with letter head which can accommodate 19 row.
I wanted to copy each 19 rows from sheet1 and paste Sheet2 one by one. data should break by Column "D".

Any help please

Thanks
Abdul
Hi, can you upload a sample file?

Thanks
 

abdulncr

Member
Thank you for your reply PCosta.

Please find the attachment, I have data listed in the Sheet named Data.
I wanted to bring the data in Sheet <Format> and print by field Entry Number.

In the Sheet Format can accommodate 19 rows, therefore If field Entry Number of Sheet Data in 19 rows or below, it should copy that rows and Print. total count should come in G34 and sum in I34. Page number 1 of 3 to come in A34.

If field Entry Number of Sheet Data more than 19 rows, it should copy 19 rows first and print then again copy 19 rows and print till there is a change in Entry Number. total count should come in G34 and sum in I34 only at last page (Change of Entry number ) Page number 1 of 3 to come in A34.

Hope I conveyed scenario, anxiously waiting for the help.

Thanks
Abdul
 

Attachments

Monty

Well-Known Member
Hello abdulncr

Please find attached as per your requirment...of course which is incomplete..
as need to understand your requirement.

This macro will copy from second sheet to first sheet first 19 rows.

Please test it and let me know..Will further enhance based on your requirment.


Hope you like this!!!!

Thanks Monty.
 

Attachments

abdulncr

Member
Hello abdulncr

Please find attached as per your requirment...of course which is incomplete..
as need to understand your requirement.

This macro will copy from second sheet to first sheet first 19 rows.

Please test it and let me know..Will further enhance based on your requirment.


Hope you like this!!!!

Thanks Monty.
Dear Monty,

Thank you for your help..

it just copy 20 line one time, please find the below requirement already detailed in the last email.

- If field Entry Number of Sheet Data in total 19 rows or below, it should copy that rows and Print. total count should come in G34 and sum in I34. Page number 1 of 3 to come in A34. after printing it will clear from format andlook for next line item

-
If field Entry Number of Sheet Data more than 19 rows, it should copy 19 rows first and print then again copy 19 rows and print till there is a change in Entry Number. total count should come in G34 and sum in I34 only at last page (Change of Entry number ) Page number 1 of 3 to come in A34.

Sample file attached here with, how the printout will look like as first page, second page and third page.

Thanks
Abdul
 

Attachments

Deepak

Excel Ninja
Check this..

Code:
Sub test2()
Const iCopyRows = 19
Dim i As Integer, copyrng As Range, iRows As Integer, iMaxPage As Integer

iRows = Sheets("Data").[A1].CurrentRegion.Rows.Count
iMaxPage = Application.Ceiling((iRows - 1) / iCopyRows, 1)

With Sheets("Format")
    For i = 2 To iRows Step iCopyRows
    Set copyrng = Sheets("Data").Range("A" & i & ":I" & i + iCopyRows - 1)
        copyrng.Copy
            .Range("A13").PasteSpecial Paste:=xlPasteValues
   
        .[A34].Value = "Page number " & (i + 17) / iCopyRows & " of " & iMaxPage
    '    .[G34].Value = Application.CountA(Application.Index(copyrng, , 9))
    '    .[I34].Value = Application.Sum(Application.Index(copyrng, , 9))
   
    .PrintOut 1, 1, 1
    Next i
    .Range("A13:I31").ClearContents
End With

End Sub
Explain it-
data should break by Column "D"
 

abdulncr

Member
Dear Sir,

Amazing...Thank you so much for your help. much appreciated.

Yes, Data should break by column D.

currently it is copying 19 rows one by one and printing without break.. below change is required in the code

-it is to break by Column 'D'.
Total in "I34" and count in "G34" at each break (it is to come only in last page of break - break is by column 'D'))
- Page number 1 of - in "A34" ( Page number calculated depends on how many pages by each break- break is by column 'D' ).

Thanks
Abdul
 

Deepak

Excel Ninja
Check this...

Code:
Sub Atest3()
Const iCopyRows = 19
Dim i As Integer, copyrng As Range, iRows As Integer, iMaxPage As Integer, DataSheet As Worksheet, L As Integer
Dim UList As Collection, UListValue As Variant

Set DataSheet = Sheets("Data")
Set UList = New Collection
With Sheets("Format")

On Error Resume Next
    For i = 2 To DataSheet.[A1].CurrentRegion.Rows.Count
        UList.Add DataSheet.Cells(i, 4), CStr(DataSheet.Cells(i, 4))
    Next i
On Error GoTo 0

.Range("A13:I31").ClearContents

For Each UListValue In UList
    DataSheet.[S1] = "Entry Number"
    DataSheet.[S2] = UListValue
    DataSheet.Range("A1").CurrentRegion.AdvancedFilter Action:=2, CriteriaRange:=DataSheet.[S1:s2], CopyToRange:=DataSheet.Range("V1"), Unique:=False
  
    iRows = DataSheet.[V1].CurrentRegion.Rows.Count
    iMaxPage = Application.Ceiling((iRows - 1) / iCopyRows, 1)

    L = 0
    For i = 2 To iRows Step iCopyRows
    L = L + 1
      
        Set copyrng = DataSheet.Range("V" & i & ":AD" & i + iCopyRows - 1)
        copyrng.Copy
            .Range("A13").PasteSpecial Paste:=xlPasteValues

        .[A34].Value = "Page number " & L & " of " & iMaxPage
      
        If iMaxPage = L Then
            .[G34].Value = Application.CountA(DataSheet.Columns("AD"))
            .[I34].Value = Application.Sum(DataSheet.Columns("AD"))
        Else
            .[G34].Value = ""
            .[I34].Value = ""
        End If
    .PrintOut 1, 1, 1
    Next i
    DataSheet.Range("V1").CurrentRegion.ClearContents
Next UListValue
    .Range("A13:I31").ClearContents
Set DataSheet = Nothing
Set UList = Nothing
End With

MsgBox "Done"
End Sub
 
Last edited:
Top