Copy row from one sheet to another based on the value in column a

[M12NIX]

Good evening all,

I have a monthly master list of members (Drinks_Test_Master) arriving at my hotel sorted in column A by date. I'm looking for a way to copy an entire row from the master sheet to another sheet, named by that date, (Drinks_Test_By_Date), to enable me to order the booze from the warehouse without it being so labor intensive. They'll be a maximum of ten new rows per day.

Then, I'll need a count of which bottles I need to order that day. You'll find a very manual COUNTIF shown within these files.

If VBA is required then I will send over the full spreadsheets. This is just a snippet of the entire report.

Hope this makes sense and thank you so very much in advance.

[dflak]

I did a couple of things with this workbook.

I put in a control panel to define a couple of things. I’m assuming that you want to do this daily for the previous day, so Cell B2 has yesterday’s date by default. You can override this by putting a date in Cell C2. The program uses the date in Cell D2.

The selected date is used to generate a sheet name in Cell B4.

The program checks to see if a sheet with this name exists. If it does it clears out the old data. Otherwise it creates a new sheet with this name.

I took your summary and moved it to the Control Panel.

I renamed the sheet with the Data to “Daily” and converted the data to an Excel table called Table_Daily. One of the advantages of Excel Tables is that they know how big they are. So as you add data, you don’t have to change formulas (such as the COUNTIF on the Pivot Table Sheet) or pivot table references.

I built a pivot table from the Daily data. Part of the code sets the filter on the date to the date in Cell D2 on the Control Panel. There is a check to see if there is actually data for the date. If not, the program puts up a warning message and stops. Otherwise it goes on to copy the data returned by the pivot table and paste it into the appropriate sheet.a

[M12NIX]

Thank you so much Dflak