ha ha ha - sheelooooooooooooooooooo great job
you started and next no one came in. Say those words " I dont know ", so
that someone will come to answer.

still waiting.... guys. thanks.


"Eddy Stan" wrote:

any help...

"Eddy Stan" wrote:

Hi Sheeloo,
I have sent my data (similar to my data).
What i do is in the sheet "letter" and what you give is in the sheet
"customer"
Input in sheet "letter B2" is taken to duelist: s1, to process my letter,
but you have to press the button to refresh the filter and show preview of
customer letter.

thanx & regards,
eddy stan



"Sheeloo" wrote:

Are you entering the custno to be picked up in cell B3?

I had assumed that customer would be filled beyond row 15 so it was clearing
it without checking... I have added the check...

I have also changed Customer Number data type to String though it should not
matter since you could run the macro.

Can you mail your file to me? add hotmail.com to my id to_sheeloo to get my
email id.

I have tested it many times and it works...

Alll it needs is Customer Number in Col A of DueList, Customer Number to be
picked in B3 of Customer...


Sub copyMacro()
Dim lastRow1, lastRow2 As Long
Dim i, k, startRow As Long

'Change Long to String in the statement below if CUSTNO are not pure numbers
Dim custNo As String

'Change 15 to the first row on CUSTOMER sheet where you want the customer
data to start
startRow = 15
k = startRow


With Worksheets("DueList")
lastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

With Worksheets("Customer")
'This looks at Col D of customer to find last filled row
lastRow2 = .Cells(.Rows.Count, "D").End(xlUp).Row
'ADDED this to clear contents only from row 15 till end IF they are filled
If lastRow2 14 Then
.Range(Cells(startRow, 1), Cells(lastRow2, 15)).ClearContents
End If
End With

custNo = Worksheets("Customer").Range("B3").Value

For i = 1 To lastRow1
If Worksheets("DueList").Cells(i, 1) = custNo Then
'Change A to the Col Letter you want to start on

With Worksheets("DueList")
.Range(.Cells(i, 1), .Cells(i, 15)).Copy _
Destination:=Worksheets("Customer").Range("A" & k)
k = k + 1
End With
End If
Next

k = k - 1
'if the data ends at row 70, then put sum at d72 total, e72 total & f72 total
Worksheets("Customer").Range("D" & (k + 2)).Value = "=Sum(D" & startRow & _
":D" & k & ")"
Worksheets("Customer").Range("E" & (k + 2)).Value = "=Sum(E" & startRow & _
":E" & k & ")"
Worksheets("Customer").Range("F" & (k + 2)).Value = "=Sum(F" & startRow & _
":F" & k & ")"

' I have left the following steps for you to write
'select c3:f75(f72+3) as print range (for preview or print);
'format the c15:f72 with borders
Worksheets("Customer").Range("A1").Select
MsgBox "Processing Complete"


End Sub



"Eddy Stan" wrote:

Hi
Thanks for the try, but please try again.
What I get is that the "Customer" sheet is cleared and sum formula is put at
row 16 thro col d16:f16
and neither data is extracted nor pasted in customer sheet.

Let me explain again.
Data sheet: duelist; data a5:p17999 , row 18000 has cells filled with
"LastRow" thro a18000:p18000
a customer no; b biz; c region; d location name; e customer uniq; f customer
name org; g pmt status; h bill no; i bill date; j mis date; k bill amount; l
paid amt; m balance due; n call date; o call amt; p remarks
target sheet: customer i require data extracted from cells
h bill no; i bill date; k bill amount; l paid amt; m balance due;p remarks
at c,d,e,f,g,h
suppose the customer no at b3 in customer sheet is found in duelist at row
15, 20, 700
i need macro to pick
h15 bill no; i15 bill date; k15 bill amount; l15 paid amt; m15 balance
due;p15 remarks
- and place at customer row 16 at c,d,e,f,g,h
h20 bill no; i20 bill date; k20 bill amount; l20 paid amt; m20 balance
due;p20 remarks
- and place at customer row 17 at c,d,e,f,g,h
h700 bill no; i700 bill date; k700 bill amount; l700 paid amt; m700 balance
due;p700 remarks
- and place at customer row 18 at c,d,e,f,g,h
and put sum of bill amt, paid amount & due amount

i tried thro' filter copy & paste, it take too much time and most of the
time excel shows "not responding
now i am doing it index() formula, still i have to paste the formula for 700
rows, as some customer has 700 bills due
hence i need this very much. pl try again. pl give notes on each command
line of macro.