+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Playa Del Carmen, Mexico
    MS-Off Ver
    2016
    Posts
    100

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,390

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

    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
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    03-05-2015
    Location
    Playa Del Carmen, Mexico
    MS-Off Ver
    2016
    Posts
    100

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

    Thank you so much Dflak

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy rows to new sheet, based on y/n value in 1st column
    By Ducksnuts in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-16-2014, 03:14 AM
  2. [SOLVED] Copy rows to new sheet based on value in Column 1
    By lzrdlvr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-12-2013, 09:15 AM
  3. Copy column from one sheet to another sheet based on date
    By thuddleston11 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-06-2012, 04:30 PM
  4. Copy column data on one sheet to row or column date on another sheet based on user in
    By soready42012 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2012, 12:03 AM
  5. Copy/paste row to another sheet based on value in column
    By tony0710 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-19-2009, 06:03 AM
  6. Copy entire row to new sheet based on value in column
    By jsperl in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-12-2008, 08:26 AM
  7. [SOLVED] copy to another sheet based on column value
    By Mike in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-04-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1