Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Generate Report based on Date Range

0

I'm feeling defeated, very defeated here. 

I need to generate a monthly report based on a date range, but I have been unable to do so on my own, or by using research from websites and other people's videos. 

Background: The attched file is part of a larger workbook, but what I'm trying to do only reflects the two tabs in this file. The End User is Exel illiterate, and so I need Sheet1 to be hidden as this will contain lots of information that will ruin the entire workbook if it is tampered with. If it were myself, I wouldn't need a Macro/UserForm to generate the report as it's as simple as Filtering, copying, and pasting into the designated sheet2. 

Specifics of what I want to accomplish: 
1. Search for all dates in date range on column "A" in Sheet1 (i.e. 12/1/18 - 12/31/18)
2. Select all rows contained in the above date range 
3. Copy the rows
4. Paste the Values of the rows into Cell A4 on sheet 2

In the attached workbook, I have 3 "actions" that I have tried, but they all seem to fail. 

 - UserForm1: I cannot get past the debugging to even see if it works. In the video "Extracting data between dates, excel vba application" (by Excel Beginners and Advanced Tips on YouTube...start at 35:40 if you want to reference it), my macro matches it with the exception of removing a 3rd lookup value. 
 - Module2: This is where I recorded the actions, but as I suspected, it will not filter the dates. Plus Sheet1 is going to be hidden, so I do not want the enduser to have access to this page. 

 - Module1: this is the only one that partially works...
(a) I cannot get it to copy/paste the entire row, it is only pasting the date cells. I also want it to paste ONLY the Values, not formmating or formulas.  
(b) It will ony paste at the very end of the table (after the "Totals" Row)...I understand why it is doing this, but I cannot seem to fix it. I want it to be pasted so it starts at Cell A4. 
(c) My preference would be to have only 1 MsgBox asking for both Start and End date, but if it will only work with 2 MsgBox's, I'm ok with that. 

I have tried many, many other ways of doing this action, but none seems to have potentional of working. I've been at this for over a week now...can you please help?? Thank you!! 

Answer
Discuss

Answers

0

Don't despair, Elaine. Fact is that you did a great job so far. But you didn't arrive at a question that can be asked and answered here. In my response I shall focus, therefore, on how to cut your project into managable parts.

The theory would have you start with an idea for the user interface (UI) and the output (Sheet2) with which the input (Sheet1) is coordinated. Data manipulation comes a distant fourth in sequence, except that the layout of both input and output must facilitate the same. That's the point at which it is easy to lose direction. When that happens, go back to the red line provided by theory.

Your output sheet doesn't seem well coordinated with the input. It seems like you were considering data manipulation in making the design (copy complete rows from Sheet1). That is worng. The output sheet should only contain those data which you want to show. You seem to be planning to copy about 4 times as many data into the output as you want to show. That will not be efficient.

Your output sheet has many hidden columns. Human curiosity leads to wanting to see what's hidden. If you want the data to be viewable on demand you are laying the groundwork here for vast waste of company time. You could easily create many different reports, all extracted from Sheet1, and all working on the same basic formula. Or you could change the UI to allow the user to select many different reports from the same basic template. Instead of allowing the user to search, give them a button to press and make sure they get exactly what they were looking for.

That brings us to the UI. The UI controls the work flow. Please be aware that you are deciding the work flow, meaning the user's efficiency, and that should rank higher in your mind than the convenience of programming. You plan on having a user form on which the user enters start and end dates. But you didn't provide a button from which to call the user form.

Your output sheet shows a "Sheet Date" ("December 2018") which I presume to be extracted from the start & end dates. This date is misleading, especially since the underlying dates aren't shown. If you want to show entries for "December" you don't need start and end dates. If you want start and end dates you should provide for them to be in different months. If you want them to define parts of a month they must be shown.

From the above thoughts a radically different design for the output sheet emerges. 

  1. Start and End date must be shown, unless you want to limit the action to select dates by month only.
  2. There must be a button to start the process.
  3. There might be a drop-down from which to select which columns are to be shown (different kidns of reports).
  4. There should be no hidden columns.
  5. Using VBA it will take more effort to manage the space above an existing total row than to create such a row on the fly. However, you might consider using a table (ListObject) which would manage the total row for you.

In my opinion, the above requirements point to no user form. The user can enter the start date, end date, in the header of Sheet2, click the button and the report gets done. You might get VBA to format the date entries as the user enters them. There might be a report selector drop-down in addition. If you want to create a table it would cover the rows 3:35 of your design. Rows 1:2 can't be merged and must be re-designed to match the established requirements.

I avoid judging the input sheet (Sheet1). Its job is to be unchangable. It's the database where all information is stored. It seems you are already rather advanced in its design. Changes to it, if they are desirable from the point of view of the output sheet, should be rejected. The database is married to data capture. It must be easy and efficient to enter data into it free of mistakes. Datasets of 130 data are definitely not easy to enter and therefore difficult to create or maintain without errors. I presume that Sheet1 is, in fact, a summary of many databases where data can be captured easily, efficiently, quickly and correctly. That, of course, is one more reason not to change it while talking about a report based on it.

At this point of the planning you now have a data source and a data destination. You also have a UI which is integrated into the output sheet. You will be ready to design a procedure that runs when the button on the report is pressed. Here is the design for it.

Sub CreateReport()
    ' Find the row of the first date in Sheet1 which is >= StartDate
    ' Find the row of the last date in Sheet1 which is <= End Date
    ' Set a range for all the rows between (and including) the two rows
    ' Assign the value of that range to an array (input array)

    ' Create an array of all the column numbers you wish to copy (copy array)
    ' Create an output array equal in size to the output form but with many extra rows
    ' Loop through all the rows in the input array
        '  Loop through all the columns in each row
        ' Transfer the value of the column to the output array if the number is in the copy array

    ' Redim the putput array to discard unused extra rows
    ' Adjust the number of rows in the output sheet
    ' Paste the output array to the output sheet
    ' Set output sheet headers, if requried
End Sub

Each of the points in the above list requires preparation and execution. Usually the preparation requires the greater effort. There may be questions regarding the preparation or the execution. The great thing is that you will be able to record steady progress as you work your way towards the end of the list. You will be able to avoid getting totally stuck.

Good luck!

Discuss

Discussion

Hi Variatus, thank you for your input. I have been overwhelmed by all the different ways to get the report to generate that I've just lost track of everything that needs to be done. Your suggested guideline will help me tremendously, I believe...but I'm sure it's going to take me another week at least figure it all out. That's not an issue as this is just for my stubborn boyfriend to make his work a lot easier and I'm not on any timeline. Plus this is helping me learn along the way. 


Elaine (rep: 4) Jan 19, '19 at 5:15 pm
A few things I want to point out in reference to your message above: 

You are correct in what I want to be shown on the report, I do not want hidden columns, I only want to display what I have shown in the headers of the output sheet. My lack of understanding on how to accomplish this made me just want to copy and paste the entire row, rather than individual columns that will take me much longer to figure out. 

I do plan on having a button on the output page that will start the process. I was just trying to get the macro to run first before I created the button. Another option you mentioned, and I like this better, is to use a drop-down menu that will designate the month that the report is to run. This form is a monthly form that will always indicate the month from the 1st to last day (i.e. January 1st - January 31st). It will also not include duplicate dates, hence the reason I have designated rows 4-35 for the output. But, I also don't mind using a table for the output, just as long as the totals at the end remain. I will redesign the table to accomodate this and your other notes. 

I'm going to go through your points listed and use this as a guidline for what needs to be done. It will take me quite a while as I'm learning as I go. I'm sure I'll be back with lots of questions. Thank you! 
Elaine (rep: 4) Jan 19, '19 at 5:16 pm
If you have a drop-down to select the month you may not need a button. The report could be created when the month is selected. But you would either need a method to set the months shown in the dropdown including year or a separate dropdown to select the year. Since you have VBA in the project I recommend to let it control the number of rows in the report. As a side question, how do you wish to handle a report for the current month created before the end of the month? How to handle holidays or other days for which there are no data? Your choices are to suppress blank rows, to fill them with zeroes or to leave them blank. I suggest to make a difference between days without data and days not included in the report.
I agree with your logic that a button isn't necessary before the code exists. But you absolutely need the final design of the output sheet before you start coding, and the design of the sheet depends upon the work flow as much as the meaning of the report itself. So, by thinking about the button now you find ways not to have it. By thinking about how to present blank lines in the context of creating a meaningful report you will avoid making such decisions later, merely for the ease of coding and to the detriment of the report's quality. Take your time. The better your design of the output report, the more you have thought out the small detail of it, the easier will be the coding in the end.
Variatus (rep: 4889) Jan 19, '19 at 8:44 pm
The more I research and try to design the report, I realize that I would like to do a dropdown list for the exact dates. By doing this, I could avoid errors of someone inputing the wrong end date, for instance 1/30/19 as opposed to 1/31/19. So far I have created a table for the fiscal year, with the start and end date of each month. I have created the dropdown list to reflect the month/year (i.e. January 2019). So now I have to have the dropdown list reflect the start and end date, and then the coding for the output of all the data.   This is for a gas station that is open 7 days a week, even on holidays, so there will be no missing dates in the report. The report will also be run around the 2nd or 3rd day of the next month, so it will always reflect the entire month. 

I understand what you are saying about having the final design, so I have been working on that and think I have it figured out. Now off to see if it works. 
Elaine (rep: 4) Jan 20, '19 at 1:31 pm
There should be no need for two drop-downs with exact start/end dates. Every February starts on Feb 1 and ends on Feb 28/29. Excel knows that. All you have to provide is month & year. Your drop-down could be dynamic, auto-filling itself with 12 months backward from the current month, preselecting last month (depending on the current date). None of this is so difficult as not to be solvable by some intelligent questioning online. The current difficulty is to allocate space to the drop-down in the header. You can't do that unless you know the widths of your columns. Then you will have to decide which columns to merge so that they appear to be in the centre, perhaps, or left or right, and you will remember that it isn't good practise to merge any cells at all and think about the consequences of that nevertheless. Then you will weigh the disadvantage of having merged cells against that of having a free-floating combo box ActiveX control.
Variatus (rep: 4889) Jan 20, '19 at 7:24 pm
Ok... so the coding was really getting to me, and I was frustrated beyond belief. I couldn't even get the 1st step to work for me. So someone suggested that I try using a PivotTable. I did not know anything about them until now... and I finally got my report to work!!  For my knowledge and future reference, I'm going to continue to work on the layout as you suggested, but this is going to take me quite a while to do. 

However, using your last suggestion, is there a way to create a dropdown list for the months and incorporate it into the PivotTable to make filtering for dates easier? Or can I only go through the PivotTable's filter to do this? 
Elaine (rep: 4) Jan 21, '19 at 1:53 pm
I would need to see the workbook to answer your question which, in any event, would appear to be different from the one we started out from in this thread.
Variatus (rep: 4889) Jan 21, '19 at 7:16 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login