Creating a command button to compile specific data

Hello everyone,

So yesterday I started building a worksheet that will be used for tracking a vast amount of data. The workbook currently has 4 sheets for each type of scenario defined in column E. I have coding so that anytime a cell in column E changes its value to a specific word, the row from A-F only, gets automatically copied to the corresponding worksheet. For example, if something is set to "Resolved" the information is copied to the "Resolved" sheet and deleted from the previous sheet. What I am looking to do, if possible, is create a command button entitled "Calculate" that when clicked on lets you select a date to calculate all the data in the workbook from sheets 1-4. Column A in my workbook has dates of when customers purchased service. I want to be able to push that command button every day at any time and calculate everything in my entire workbook in sheets 1-4 from any date at my choosing, and have it copy to a 5th sheet named "Daily". I have found some code that copies everything in all my workbooks to the summary page, but am unable to get it so that I can choose so that only a certain date is copied over. I am very new to VBA and am hoping to learn more throughout my experiences here. Fortunately I had an issue yesterday that was resolved so I appreciate any and all help. Thank you

What do you want to do with the data once it is copied over to the Daily sheet? That is, what are we calculating?

Well anyway, here's the code based on what you described.

Insert an ActiveX button, then change the button name in the first line of my code to match whatever you name your button, (In Design Mode, right click on button --> Properties, then in the Name field, Name your button.)

This code belongs on the same sheet object that the button is on.

Please Login or Register  to view this content.

Ah, it is you again! What I am hoping to do is basically just send out a daily report of everything that was added to the sheet on X day. I will be able to see all "Resolved", "Open", etc. I will try this out right now!

2 things

-when running the script what is the format to type in the date:
-I am getting a type mismatch

I do also see in your line of code this section:
ws1.Cells.Delete

is this going to delete the cells, because I am only looking to copy over to the last sheet

ws1.Cells.Delete deletes all of the cells on the "Daily" worksheet. That way, next time you press the button, it isn't overwriting the last run, or leaving the last run on there.

Example if you didn't have that line: you run the report for 2/12/2015 and get 10 results. You run it again for 2/13/2015 and get 2 results. In that case the first two rows would show 2/13/2015, and the next 8 would show 2/12/2015 because they were never deleted off that sheet. Make sense?

The format of your date should be whatever the format is in column A. Type mismatch is probably something else, though. do your dates start in row 2?

Oh okay, that makes more sense.
They start in row 3. Sorry, forgot to clarify. They start in row 3 for all 4 sheets. The daily sheet is actually on sheet 5, so should I change the data to ws5.Cells.Delete?

They start in row 3 for all 4 sheets.

Then change For i = 2 To LastRow to For i = 3 To LastRow

should I change the data to ws5.Cells.Delete

Nope! Well you could, but ws1 is a variable, and an arbitrary one at that. Doing Set ws1 = Worksheets("Daily") means ws1 is now the Worksheet "Daily". If you really want, you could change all the ws1's to ws5's, but like I said it's an arbitrary variable name.

Okay great. So I have a question. I run the code and it works great. Is there a way that I can change it to only copy the data from the row for A-F? Also I have a layout for the data to get copied to, and whenever I run the command button the layout is deleted. I'll attach a before and after photo

Before I run command button:
pic1.png
After I run it:
pic2.png

Perfect! this is exactly what I needed. Thank you so much again for your quick resolutions!

Great! Any reputation points would be appreciated.

Edit: Duplicate Post