Previous Topic Next Topic

I need your help with Macro's please

Closed
Enslinj - May 30, 2016 at 09:09 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 2, 2016 at 11:13 AM
Hi there,
I have been doing some reading up & watching YouTube tutorials regarding Macro's that will copy data from one sheet to another if certain criteria are met.

I must admit I haven't had much luck achieving what I am after & thought I would to turn to the experts for some assistance.

Sheet 1 = A Inventory list that represents all the items I shop for in a month.
This sheet contains item description, quantity, unit price & cost.

Sheet 2 = Shopping list. Only items that either have a quantity or cost greater than 0 should be copied to this sheet.

Functionality:
Sheet 1 is the sheet I would use to see run numbers, assesses affordability & see what I buy & what I leave for the next month based on the budget / total cost of selection.

Ideally after this exercise I want to click a control button which will generate a the shopping list (sheet 2) based on the above mentioned criteria. It should copy the entire row's data or at the very least the item name & quantity.

I don't know if what I am after is feasible or where to to start. Some advice please?

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 30, 2016 at 12:04 PM
Hi Enslinj,

Let me start by apologizing for not showing your message the moment you posted. Apparently the word "Shopping" is blacklisted on this site and therefore your post didn't show.

For your query I would like to know which column you use for quantity on sheet 1. Also provide the sheet names for both sheets.

Best regards,
Trowa
0
Answer 2 / 3
Enslinj Posts 2 Registration date Monday May 30, 2016 Status Member Last seen June 1, 2016
May 31, 2016 at 01:47 AM
Hi There,

Wow, thank you for the prompt reply.
I am going to attach a screen grab of the sheet which will hopefully answer the questions a bit better.

I am opting a "landscape" layout approach so I minimize the need for scrolling up & down like I would have if I listed everything below each other.

I know this complicates things, but it's not a must have.
If everything needs to be listed beneath each other that's also perfectly fine.

0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 31, 2016 at 12:01 PM
Hi Enslinj,

So this is a fixed list, right? No rows or category will be added.

Do you want your Shopping list to look the same as your Order form (without the 0 quantities) or did you have a different format in mind. Post another screen grab when the latter is the case.

Best regards,
Trowa
0
Enslinj Posts 2 Registration date Monday May 30, 2016 Status Member Last seen June 1, 2016 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Jun 1, 2016 at 05:10 AM
Hi there,

Yes, it is a fixed list. The amount of categories wont change.
In terms of rows, I would need the flexibility to add items, but my idea was to have a Max number of items. Let's say 25 rows per category. If there aren't 25 elements I can hide the empty rows? That wont mess with the formulas but I can keep list as short as it needs to be.

The same format would be amazing, I would have also been happy with just one long list.

Thanks for all your time & effort on this matter.

I can create an excel spreadsheet which can be used as the template if that would help?
Just not sure how I send it to you. heheh

Regards

Enslin
0
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 2, 2016 at 11:13 AM
Hi Enslinj,

How about this format?:


This way you can filter out the zero quantities, which will hide those rows:


At the same time you can filter on category as well. So you can see the shopping list (with quatities bigger then zero) for only category 1 for example:


This does create a long list, but 3 categories times 25 items won't fit on your screen anyway and now you will be able to filter your data. You can copy the filtered data to a new sheet each month to create monthly shopping lists.

Let me know your thoughts.

Best regards,
Trowa

0