Modify This Code for WorkBook Loop

[Pukka83]

As you can see the code below, everything is the same, except
template(x).xls
where x is 1,2,3 and so on.

Can someone help me to modify this code so it'll look through every template(x).xls workbook in the same folder?

as an additonal question, when I use the below below on a new worksheet, it starts at line 2, is there a way to start at line1 or line 5?

Please Login or Register  to view this content.

[rylo]

Hi

1) Didn't test this but here goes.

Please Login or Register  to view this content.

Note: this copies the data from columns A:D. In your code, you do this for template1.xls, but then only copy columns A:C, yet output the last quantity from the first file. Is that correct???

2) I've commented out some code that should start the output in row 5 (or some nominated row above 2).

3) If you want to start at row 1, the easiest way I've found is to have a boolean variable (say fone) that is set to true. Then test on that variable. If it is true, then (a) use 1 as the output row (b) turn it to false (c) use the normal offset approach for the rest of the output.

HTH

rylo

[Pukka83]

Thank you for prompt reply!
forgot to mention something...

it is template(*).xls
rather than template*.xls

so I just modify: filess = Dir("template*.xls")
to Dir("template(*).xls")
?

oh, the reason I did the compliated looping over copy and paste is because the first 4 rows I do not want to copy.

hmm, your copy and paste jsut overlaps each other.
in this case I got 3 sheets template1.xls, template2.xls, template3.xls
template2 overwrites template1, template 3 overwrites template2
result: template3 only.

[rylo]

Hi

I put the attached file and 3 sample workbooks (template(1).xls, template(2).xls and template(3).xls) in the same directory.

Opened up the attached file and ran the code. All the data was output correctly.

Can you try, and if you are still getting problems, attach the 3 template workbooks you are using.

rylo

[Pukka83]

Hi sorry the the trouble. It does display, but has empty spaces inbetween and abit incorrect. It's not suppose to show the headers.

attached is the files.

[rylo]

Hi

OK try this. I've also changed the output to show the values rather than the formulas.

Please Login or Register  to view this content.

rylo

[Pukka83]

Hi, thanks for your reply. For some odd reason it still display funnigly. Anyway I modified it to make it work somehow.

I managed to do the combine the row if column B matches. However column D(quantity, number value), i want the quantity to add-up if column B matches. Any idea how do I modify the code below to do that?

for example:
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30
TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30

will combine to become
TDG-**002 Tuna Cheese Pizza Bar (KG) KG 60 <<<< combined
TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30 <<<< left untouched


Taken from the code below:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Pukka83]

*hadouken*

[Pukka83]

*hadouken*

[rylo]

Hi

Sorry but had no idea what *hadouken* meant. I'm presuming it was a BUMP.

Try this revised approach.

Please Login or Register  to view this content.

rylo

[Pukka83]

thank you very much! I'll try it out now

well nowadays you can wiki everything:
http://en.wikipedia.org/wiki/Hadouken


it is a fireball move(signature move) done by the fighters ryu, ken in street fighter

[Pukka83]

tried your code, it says out of range...

anyway i found that the below code works, myabe I just need it to combine the quantity values before it deletes the duplicates. any idea?

Please Login or Register  to view this content.

[Pukka83]

*hadouken*

[rylo]

Hi

Where is it giving the out of range error?

rylo

[Pukka83]

hi, overhere:

Range("J2").AutoFill Destination:=Range("J2:J" & Cells(Rows.Count, "G").End(xlUp).Row)

[rylo]

Hi

Is there any data being imported from your source files? Is there actually any data copied across to columns G:I?

rylo

[Pukka83]

erm, i mean, no. But I'm trying to avoid using, in case of expansion

(you can check back at the zip file)
template(1).xls
template(2).xls
template(3).xls

[rylo]

Hi

Are the source files and the main controlling file in the same directory? Are any of the source files being opened when you run the macro?

I tested using the source files from your original zip file, and when things were all done, I was left with
Product ID Product Uom Qty
TDG-**002 Tuna Cheese Pizza Bar KG 90
RFG-**588 Thai Chicken Fried Rice KG 90
RFG-**502 Club Sandwich PKT 18
RFG-**501 Roast Mexican Chicken PKT 90
RFG-**501 Roast Mexican Chicken pcs 72
TDG-**002 Tuna Cheese Pizza Bar pcs 90
RFG-**501 Roast Mexican Chicken KG 60
in the sheet GrandTotal.


rylo

[Pukka83]

yup, that's it. That's the output I want. Just funny that it didn't work for me.



i found also another source code that combines duplicates but I got problem modifying it to suit my needs that us using bounds

Please Login or Register  to view this content.

[jindon]

Sorry guys. this is a CROSS POST.

This guy is hopeless.

[rylo]

Jindon

Got a link reference? And does he have a solution on the other site?

rylo

[Pukka83]

yah, there wasn't a reply for a few days that's why I did one here, secondary one at mr.excel(I did mention over there it is linked),
the original while the other at:
http://www.mrexcel.com/forum/showthr...07#post1603707


Really sorry, never came to my mind to link the original to the secondary.

rylo: no solutions from the other side (other than pivot table).

[rylo]

and the answers to my questions are....

[jindon]

I can not give the url, because I'm not allowed to copy the url due to the security here.
However you can easily find the thread under the name of Guanjin, Peter and the title is
Modify this Code to add-up numbers in COL-D in MrExcel

Just a note for you, the OP always tend to give us the crue one by one and lead us to the different direction. So I have decided not to answer his thread anymore.

[Pukka83]

rylon: The soultions you gave, my partner is using them. I'm just helping my partner with her work. So we can finish this project in time.

Thanks for your understanding.

[davesexcel]

Sorry guys. this is a CROSS POST.

This guy is hopeless.

This is a very offending statement... however, I don't believe I have ever read a more confusing thread, hopefully it will all get sorted out.

[Pukka83]

erm, it's alrite
I did cause him alot of trouble because of the numberous changes my company made, that I need to adjust my code to.
just hope I can finish this soon :>

[jindon]

This is a very offending statement... however, I don't believe I have ever read a more confusing thread, hopefully it will all get sorted out.

Hope you will be able to sort this out.

[rylo]

All

I'm taking

The soultions you gave, my partner is using them

to mean a working solution has been found, and closing the thread.

rylo

[Simon Lloyd]

Also cross posted here http://www.mrexcel.com/forum/showthr...=1#post1603890

[Simon Lloyd]

User Banned!