Dear Sir



We would like to make a small programme which
copy some details from named "products details" file
into named "breakdown" file, the result will be as
named "breakdown-result" file

we will copy as follow steps

for "products details" file
for mark no : r1, we will copy the details from "products details" and paste into sheet 1 in breakdown file

for mark no: r2, we will copy the details from "products details" and paste into sheet 2 in breakdown file

that is
r1 paste to sheet 1 in breakdown file
r2 paste to sheet 2 in breakdown file

Then the result will be as name "breakdown-result" file

when we open the file "breakdown","products details" file,
how to write the the VBA code, then programe will automatically
copy and paste

sub a()

"HOW TO WRITE THE VBA CODE"

end sub

Thanks
Tom

Attached Files

	breakdown.xls (19.0 KB, 7 views)
	breakdown-result.xls (20.0 KB, 6 views)
	products details.xls (17.5 KB, 8 views)

Seems to me the best way to start, if you don't already know VBA, is to start recording a macro; then do all the actions you want included, while the recording mechanism "watches". When you're done, stop recording, and look at the macro that Excel created. You'll have to modify it from there to make it more general; we'll have to talk about that.

I expect you'll have questions. Do you know how to record a macro? Once you've recorded a macro, do you know how to find and display it? And once you're looking at the macro instructions, do you have any programming background that will help you understand how it works?

Hi thanks for your help
but if the mark no , no of r1,r2 is different from time to time

for example
r1
r1
r2
r2
r2

next time is
r1
r1
r1
r1
r2

Then the macro recording cannot use.
then how to write the vba code ....maybe use if ...then...

Thanks
Tom

Correct. I would do it that way: Record the macro for just one set of cells. Then you can look at that program, and learn from it how those VBA commands should be written. With a little work (and I'll help), you should then be able to write IF statements that will enable your program to know how to do the same thing for the right cells next time.

Hi Tom,

Contrary to previous advice, the macro recorder will be of little use here and, even if it was, the code it produces tends to be very inefficient.

I've had a look at your "products details" file and have some questions: Are the data always sorted by r1, r2, etc? Since your breakdown file is empty, except for having nine worksheets, is there a reason you need to work with that rather than, say, creating a new workbook on the fly and adding worksheets as necessary?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]