I need your help with Macro's please [Closed]

Report
-
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
-
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 replies

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
2
Registration date
Monday May 30, 2016
Status
Member
Last seen
June 1, 2016

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.

Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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
Posts
2
Registration date
Monday May 30, 2016
Status
Member
Last seen
June 1, 2016
>
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021

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
Posts
2757
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 8, 2021
462
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!