macro to find data from one sheet & copy in another sheet

[Eddy Stan]

hi
Kindly help me with macro to save time & file size.
I am having customer bills listed in duelist sheet rows 18000
listed date wise, the columns headings at 5 as cust-no, biz, region,
location, custgroup, customer, bildate, misdate, bilamt, paidamt, due amt and
remarks
Now in customer sheet-Customer number will be put at cell b3
(rows 4 to 14 has address subject etc.,) i have column title at row15
macro to be place in a button.
macro start
i need customer no# in b3 found in range cust_no in the duelist sheet, then
from duelist sheet copy matching row's, billno, billdate, billamt, paidamt,
balamt, remarks and paste in query sheet start at row 15 column
c15(billdate),d15(billamount),e15(paidamt),f15(due amt), g15 (remarks), then
next find at row 16,...so on. suppose
if the data ends at row 70, then put sum at d72 total, e72 total & f72 total
then
select c3:f75(f72+3) as print range (for preview or print);
format the c15:f72 with borders
then
close macro
i have done this by putting formulas from row 15 to row 700, expecting
maximum due bills 685. Used formula in duelist in afresh column to find
matching rows.
Then picked them in customer sheet
so the file size is very big like 40mb so take much time. i have used macro
to print customer letter with bill details, but it take much time. i hope
macro will take less space & time.
advance thanks for help, quick help is appreciated.

 

[Sheeloo]

Try the macro below
(See inline comments for explanation)

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 Long

'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")
lastRow2 = .Cells(.Rows.Count, "D").End(xlUp).Row
.Range(Cells(startRow, 1), Cells(lastRow2, 15)).ClearContents
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 &
"" & 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]

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 a517999 , row 18000 has cells filled with
"LastRow" thro a1800018000
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.

 

[Sheeloo]

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 & _
"" & 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]

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

 

[Eddy Stan]

any help...

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

 

[Eddy Stan]

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.