[polly06]

Hi i want to create a macro for a shopping list. I have a main list by which i place orders from, but i don't order everything on this list. I want to be able to take the items i have ordered and put them in another worksheet so i can then fax just the items i have order off to the supplier. I have no idea how to do this. Can someone please help me.

Thanks

 

[Jimmy the Hand]

Hi and welcome to TSG!

This info is not enough to give a full solution. I guess you want this in Excel, and I can assume some more things, but you shouldn't leave these to our imagination. Please give us explicit details, the more, the better. Best way is to upload a small sample workbook which contains only dummy data, but shows both the current state and what you want to accomplish.

 

[polly06]

I am sorry i didnt give enough information this is all new to me.

I have uploaded a simple copy of what i want to achieve and hopefully you can get some idea as to what i want to do. Basically i want to get what is ordered in each department (tabs along the bottom) to the Total Supplies Tab so that i can fax just that page to the supplier.

I hope this make a little more sense.

Thanks

 

[Jimmy the Hand]

Hi,

Try the macro called "Extract" in the attached workbook.
For it to work I needed to "uniformize" the sheets, which means that I deleted Column A in one of the sheets, I think it was the "Fruit & Veg". I did this to let ordered amounts and item group names be in the same column on each sheet (C and A, respectively)

Jimmy

EDIT:
I just remembered something.
In the code there is a such section:

    With Application.FindFormat.Font
        .FontStyle = "Félkövér"
        .Underline = xlUnderlineStyleSingle
    End With

Now, "Félkövér" means "Bold" in Hungarian. This probably won't work for you, so please correct this error before running the macro:

    With Application.FindFormat.Font
        .FontStyle = "Bold"
        .Underline = xlUnderlineStyleSingle
    End With

 

[polly06]

Thank you so much that is just what i needed.

I need to do a course so that i don't have to rely on your knowledge in future

Kind regards

Sharon

 

[Jimmy the Hand]

Sharon, you are very welcome.
Actually I've learned a few things while working on your problem, so it was worth for me, too. One thing you shouldn't forget:
There's nothing wrong with relying on other people's knowledge. TSG forum exist for this very reason, actually. Still, it's better to know than to ask, I must agree.

Take care

Jimmy

 

[polly06]

Jimmy just one thing, i have now adapted what you did for me on to the original spreadsheet that has more lines. But when i run the macro it repeats itself. I get the same information twice. Can you please let me know why it is doing this.

Thanks again Sharon

 

[Jimmy the Hand]

Jimmy just one thing, i have now adapted what you did for me on to the original spreadsheet that has more lines. But when i run the macro it repeats itself. I get the same information twice. Can you please let me know why it is doing this.

Thanks again Sharon

It's very hard to tell without seeing the modifications you did. My best guess is that the macro I wrode didn't include deleting the contents of sheet "Total Supplies". It appends new rows to the end of the sheet's content. What you see may look like double run, but in fact it's the result of two runs. Try clearing all from Total Supplies first.

If that solves it, let me know. I'll update the macro to include that functionality. Otherwise, I need to see how you modified the code.

Jimmy

 

[polly06]

I haven't done too many adustment i don't think but i don't totally understand the whole macros thingy and that is why i am here writing to you i guess.

Can you have a look for me and let me know what it is i have done wrong

Thanks again for your time

Sharon

 

[Jimmy the Hand]

Hi Sharon,

You did nothing wrong. The problem roots in renaming the last sheet from "Total supplies" to "Total Supplies". I used a loop in the code, that processed all sheets except "Front Page" and "Total supplies". This change of name resulted in the macro processing sheet "Total Supplies" as well, effectively copying it's contents onto itself.

But I repeat, you did nothing wrong. It's just natural that you may want to rename sheets or change their order. It is I who should've expected that and make a code that can deal with such changes. I've revised all macros, including the ones that you recorded to delete some things from the sheets.

The main code now uses CodeName property of the sheets, which doesn't change so easily, also is invisible for regular user, so there's not much point in changing it. Now you can rename all sheets as you like, the code will work. One thing will kill it, however: if you replace sheet "Total Supplies" with a new one. (I.e. delete the old one, then insert a new sheet, and rename it to "Total Supplies".) I also renamed the macro "Extract" to "CreateExtract". You see, assigned macro of the first button had a Hungarian name, when I downloaded your post. The only explanation I could come up with was that "Extract" may be a reserved word, or something, and it was translated automatically, in order to agree with local settings.

Reset Page button deletes all contents of "Total Supplies", but does the job in one step, and without jumping back and forth between sheets.

Clear to Zero code has been replaced. Now it deletes column C only from C2 downwards. So, you can keep the "Order" label in C1, as was in the previous version, if you want.

Jimmy

 

[OBP]

Jimmy, would this be under better control if it was in Access?

 

[Jimmy the Hand]

Tony, I've been expecting you

Access could be better from certain points of view. I think, first of all, of the numerous reporting possibilities that are native to Access, but would be hard to achieve in Excel. Probably it would be more fool-proof, too.
On the other hand, my first thought was that in Excel it's extremely easy to make the orders. You just put a few numbers in certain cells, and that's it. I couldn't come up with any idea, how I could easily create an interface to do this.

In the end, all comes down to what Sharon, or her employer, wants, and what are their possibilities. But I agree that she must know that there are other ways as well to do this.

Jimmy

 

[OBP]

My concern was that Excel is so easy to "play with" and therefore Macros are very susceptible to being wrecked by inadvertent changes.

 

[polly06]

Thanks so much for your help in getting this to work. It is going to make the job a whole lot easier. I didnt really consider doing it in access but Tony is probably right it would be less likely to be corrupted. Problem being the people i want to use this just need something basic to play around with.

Just one question, i loaded it onto another computer and when they ran it the got a debugger error, what causes that to happen??

Thanks again for all your help, i am sure i will be back with more questions in the future as i try and streamline some of the jobs that are done around here.

Regards Sharon

 

[Jimmy the Hand]

I checked the macro again, but found no problems. So, again I need more info.
1) What is the exact error message?
2) When code run breaks, and Excel displays the error message, have them people click on debug button, and tell me which line of code is higlighted in yellow. (That line causes the error.)

Anyway, you are welcome to any help, just as I said before. If you start new threads concerning Microsoft Office applications, I suggest you do it in the Business Applications forum, because that's the place Excel/Access experts visit most regularly. For example, there are exceptional Excel talents there, who seldom, if ever, appear here at Development forum. If I can't find the error you speak of, probably I'll call over someone such.

Jimmy

 

[OBP]

Jimmy, it might be Reference Library settings, that is a favourite one when changing computers.
Sharon, "just play around with" sounds a problem where Excel is concerned, especially if they start adding rows, columns, sheets etc. Hard coded Macros have to be very very versatile to deal with that kind of thing.