Shopping list macro help

**JDW96** · 02-09-2018, 11:02 AM

Hi everyone!

I'm fairly new to macros and VBA - so please excuse any stupid questions - but I'm hoping you can help me out.

I'm trying to create a macro which will add selected items to a shopping list. When I click the "+" icon next to the item I want that item to be added to the shopping list on a different sheet, on the line below whatever has previously been added.

On the attached document please see the first and second sheets which demonstrate what I'm trying to do.

If what I am asking for is an easy task for you, and you wantsomething a little more challenging to get stuck into I have also added sheets 3 and 4, which show how I would have the sheet running in a perfect world. The add symbol would open up a pop-up which would allow me to enter in the quantity and units required. Pressing the "Add" button would transfer all of this data into the shopping list.

I don't know if this is something which is possible, and I would be very happy if it only worked as I suggested in the more simple format, but if you could manage to create the pop-up that would be amazing.

Thank you for your time, it is greatly appreciated and I hope you are able to help.

Many thanks!

**mehmetcik** · 02-09-2018, 11:39 AM

Why So complicated?

Type the quantity in column B Entering Return could copy that line to the shopping list, but is pointless.

Once you have entered your quantities click on a button to create the shopping list..... Much Better

This Macro would do what you need, Just assign it to a button.

Sub Macro1()
    Sheets("Shopping List").Cells.ClearContents
    Columns("C:C").SpecialCells(xlCellTypeConstants, 1).Select
    Selection.EntireRow.Copy Destination:=Sheets("Shopping List").Range("A1")
End Sub

Better Still Assign it to a worksheet activation Macro


Private Sub Worksheet_Activate()
Sheets("All Items").Select
Macro1

End Sub

This Code is modified to clear the shopping list automatically


Sub Macro1()
    Sheets("Shopping List").Cells.ClearContents
    Columns("C:C").SpecialCells(xlCellTypeConstants, 1).Select
    Selection.EntireRow.Copy Destination:=Sheets("Shopping List").Range("A1")
    LR = Cells(Rows.Count, 3).End(xlUp).Row
    Range("C2:C" & LR).Clear
    Range("A1").Select
End Sub

**JDW96** · 02-09-2018, 12:01 PM

Originally Posted by mehmetcik

Why So complicated?

Type the quantity in column B Entering Return could copy that line to the shopping list, but is pointless.

Once you have entered your quantities click on a button to create the shopping list..... Much Better

Thanks for taking a look. Yeah sure I appreciate that your method would be more simple, but the actual spreadsheet I would be applying this to could grow to having thousands of lines for the items, so I would prefer to be able to deal with each line on an individual basis. I may have to switch to your method if the way I would prefer to do it is not possible however.

**mehmetcik** · 02-09-2018, 12:08 PM

If you are going to have thousands of lines then you should also look at using Groups.

I created a couple of macros a few years back that you might like

**JDW96** · 02-09-2018, 12:41 PM

Originally Posted by mehmetcik

If you are going to have thousands of lines then you should also look at using Groups.

I created a couple of macros a few years back that you might like

Thanks for your help, I'll look into it

Shopping list macro help

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Shopping list macro help

Re: Shopping list macro help

Re: Shopping list macro help

Re: Shopping list macro help

Re: Shopping list macro help

Thread Information

Users Browsing this Thread

Similar Threads

Making shopping list work for me

Create shopping list from drop down menu

Shopping list creator

Shopping List Calculator / Budgeting Spreadsheet

Shopping list

Itemized shopping list

Excel Shopping List

Bookmarks

Bookmarks

Posting Permissions