Tech Support Guy banner

Macro for shopping list

2769 Views 15 Replies 3 Participants Last post by  OBP
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
Status
Not open for further replies.
1 - 7 of 16 Posts
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.
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:
Code:
    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:
Code:
    With Application.FindFormat.Font
        .FontStyle = "Bold"
        .Underline = xlUnderlineStyleSingle
    End With

Attachments

See less See more
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 said:
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
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

Attachments

See less See more
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
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
1 - 7 of 16 Posts
Status
Not open for further replies.
Top