There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
 
Tag Cloud
acer black screen boot bsod computer connection crash css dell display driver drivers email error ethernet excel explorer firefox firefox 3 game hard drive internet internet explorer itunes laptop linux malware monitor network networking nvidia outlook outlook 2003 outlook express partition password printer problem router slow software sound trojan usb video virus vista windows windows xp wireless
Software Development
Search
Search in:
 
Advanced Search
Tech Support Guy Forums > Software & Hardware > Software Development >
Macro for shopping list


HELLO AND WELCOME! Before you can post your question, you'll have to register -- it's completely free! Click here to join today! We highly recommend that you print a copy of our Guide for New Members. Enjoy!

Closed Thread
 
Thread Tools
polly06's Avatar
Junior Member with 6 posts.
 
Join Date: Jan 2007
Experience: Intermediate
02-Jan-2007, 12:50 AM #1
Macro for shopping list
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's Avatar
Senior Member with 794 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: With Excel, fairly good.
02-Jan-2007, 01:02 AM #2
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.
__________________
'
It is advised to provide a clear, detailed description of the task, so that others can understand it, and offer the best possible help. Otherwise, you risk experts ignoring your request.
polly06's Avatar
Junior Member with 6 posts.
 
Join Date: Jan 2007
Experience: Intermediate
02-Jan-2007, 04:49 PM #3
Smile macro for shopping list
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
Attached Files
File Type: xls Shopping List.xls (84.0 KB, 42 views)
Jimmy the Hand's Avatar
Senior Member with 794 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: With Excel, fairly good.
03-Jan-2007, 03:10 AM #4
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
Attached Files
File Type: xls Shopping List.xls (107.0 KB, 38 views)
__________________
'
It is advised to provide a clear, detailed description of the task, so that others can understand it, and offer the best possible help. Otherwise, you risk experts ignoring your request.

Last edited by Jimmy the Hand : 03-Jan-2007 03:19 AM.
polly06's Avatar
Junior Member with 6 posts.
 
Join Date: Jan 2007
Experience: Intermediate
03-Jan-2007, 04:14 PM #5
macro for shopping list
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's Avatar
Senior Member with 794 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: With Excel, fairly good.
03-Jan-2007, 05:04 PM #6
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
__________________
'
It is advised to provide a clear, detailed description of the task, so that others can understand it, and offer the best possible help. Otherwise, you risk experts ignoring your request.
polly06's Avatar
Junior Member with 6 posts.
 
Join Date: Jan 2007
Experience: Intermediate
03-Jan-2007, 06:03 PM #7
macro for shopping list
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's Avatar
Senior Member with 794 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: With Excel, fairly good.
03-Jan-2007, 10:57 PM #8
Quote:
Originally Posted by 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

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
__________________
'
It is advised to provide a clear, detailed description of the task, so that others can understand it, and offer the best possible help. Otherwise, you risk experts ignoring your request.
polly06's Avatar
Junior Member with 6 posts.
 
Join Date: Jan 2007
Experience: Intermediate
03-Jan-2007, 11:26 PM #9
macro for shopping list
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
Attached Files
File Type: xls Requisition2 Orders.xls (198.5 KB, 39 views)
Jimmy the Hand's Avatar
Senior Member with 794 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: With Excel, fairly good.
04-Jan-2007, 02:28 AM #10
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
Attached Files
File Type: xls Requisition2 Orders.xls (197.0 KB, 28 views)
__________________
'
It is advised to provide a clear, detailed description of the task, so that others can understand it, and offer the best possible help. Otherwise, you risk experts ignoring your request.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,560 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
04-Jan-2007, 07:28 AM #11
Jimmy, would this be under better control if it was in Access?
Jimmy the Hand's Avatar
Senior Member with 794 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: With Excel, fairly good.
04-Jan-2007, 08:11 AM #12
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
__________________
'
It is advised to provide a clear, detailed description of the task, so that others can understand it, and offer the best possible help. Otherwise, you risk experts ignoring your request.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,560 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
04-Jan-2007, 01:25 PM #13
My concern was that Excel is so easy to "play with" and therefore Macros are very susceptible to being wrecked by inadvertent changes.
polly06's Avatar
Junior Member with 6 posts.
 
Join Date: Jan 2007
Experience: Intermediate
04-Jan-2007, 06:07 PM #14
macro for shopping list
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's Avatar
Senior Member with 794 posts.
 
Join Date: Jul 2006
Location: Hungary
Experience: With Excel, fairly good.
04-Jan-2007, 11:30 PM #15
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
__________________
'
It is advised to provide a clear, detailed description of the task, so that others can understand it, and offer the best possible help. Otherwise, you risk experts ignoring your request.

Last edited by Jimmy the Hand : 05-Jan-2007 07:32 AM. Reason: wrong spelling
Closed Thread

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who help people like you solve computer problems. See our Welcome Guide to get started.



Thread Tools


You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 03:20 AM.
Copyright © 1996 - 2008 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Powered by Cermak Technologies, Inc.