VBA report with forms

At work I deal with a large amounts of sales data and the only tools I use to filter data and create reports are pivot tables but sometimes pivot tables won't give me the details I want the way I want. any time my boss asks me for a monthly report I have to do everything manually from the pivot table and insert the data on another table where I put the filteres data. I want to be able to get a monthly sales report per store that will populate itself in a daily basis, I also want to be able to select a store, style, size and date from a form in Excel and see the report in a sheet. Aproblem that I have found in order to use the size as a parameter is that the size comes in the same line as the item description and do not know how to make excel to check just for the size in the description field.
The attached file contains sales data for a better understanding of my case.
I will really appreciate some help in this matter.

Thanks

Moroformat

Please change your thread Title. Please specify your problem ...

and where is the PT?

PT Pivot Table and have you looked at http://www.contextures.com/xlPivot01.html

Hi Moroformat
Are you asking for two things here?

I want to be able to get a monthly sales report per store that will populate itself in a daily basis

When you say "auto-populate on a daily basis", where will the information come from?

I also want to be able to select a store, style, size and date from a form in Excel and see the report in a sheet.

What is the "Style"? Is it this

White/White Cap Sleeve

What does the report you attached represent? Is this the daily report?

How about creating an example worksheet for each of the above so I can get a picture of what your output would look like.
John

You can group data in a Pivottable by Month, Quarters and Years. A VBA solution would not be as quick oe efficient asa PivotTable

Here's a quick example using your data

Hi moroformat
This code will split size and style into two different columns with your sample data.

Please Login or Register  to view this content.

I'll be glad to look at the other issues with your feedback; alternately, look at royUK's pivot table solution.
Let me know of issues.
John

Hello jaslake,

Thank you so much for taking the time to look at my little puzzle. In response to your inquires :

1.All the Data will be taken from the table in the file I attached which is nothing more than a daily sales report our client sends to us every day.

2. Basically I want to generate a monthly summary report per show like the one I am just attaching with the name "Summary" and also a more detailed like the one I also attached named "Detailed_Monthly_Report". I Currently populate these two reports individually by creating a pivot table and then getting the numbers from the pivot table to the "summary" and "Detailed_Monthly_Report" reports which is very time consuming and inefficient. I would like for the "summary" and "Detailed_Monthly_Report" reports to populate daily as i add more data to the sales table so I can also be able to take better decisions at work.
The most important thing I would like you to help me with is a VBA program in a different sheet with forms that will allow for my boss and me to filter or access data from both the "Summary" and the "Detailed_Monthly_Report" reports in a more concise and fast way after selecting certain parameters.

3. White/White Cap Sleeve - In this case, the style would be "Cap Sleeve". The style is everything other than the colors.

Thank you for everything and I would really appreciate it if you could help me.

Moroformat

Hi monoformat

Either we're not communicating or I'm not understanding (probably the latter). You've attached a file (Sales-2009) for the months of Oct 08 thru Jan 09. You also attached a file (Summary_2009) that appears to include Jun 2009 thru Sept 2009. These files appear to have no relationship to each other. I can find few (if any) "stores" on both lists.
With regard to the Detailed Monthly sheet, what is the "South Central Show"? Where does that come from? There's no reference to that in the other two sheets that I can see.
You had mentioned this

Please Login or Register  to view this content.

I assume you want this on the summary sheet but your example doesn't reflect that.
With regard to stores; what is the key: Store or Warehouse?
With regard to the "Summary Sheet"; will it look like your posted sheet?
Based on what you've posted, I'm not understanding. I'm a linear thinker. There are others on this site that are perfectly capable of thinking "out of the box". It appears you've given me what you have. Give me a co-ordinated example of what you want to see. I need more direction.
John

Hi moroformat
I'm going to noodle on this for a day or so to come up with some ideas. As soon as I have something for you to look at, I'll post it. I'll probably do this in steps because it could get quite complex. I would hate to move too far down the road and find out it doesn't suit your needs.
I'll be in touch.
John

I can't understand why you don't use a PivotTable. I've posted an example that shows you how to get monthly & quarterly figures.

Hi moroformat
Still noodling. I should have a first pass for the "Summary" file in the next day or two. I have the "Summary" report pretty well figured out. I'm working on the User Input to chose what month they wish to see. As soon as I have something I'm comfortable with, I'll give it to you for your review and input.
John

Originally Posted by moroformat

royUK,

I am currently using pivot tables to get the info for my report. The problem with pivot tables is that they are not as detailed and sometimes they cannot give the parameters I want besides the fact that the officers of the company I work for do not like using pivot tables. I am trying to create a VBA form report that my bosses can access easily and get all the info they need.

any other suggestions?

Thanks,

Moroformat

PivotTables are one of the best data tools in Excel, if you want details of a particular result double clicking will generate list of data that makes up that resul in a separate sheet.

Hi moroformat
The attached workbook contains code that will create a summary sheet for store sales based on the month the user selects. I'd like you to look at it and tell me if I'm heading in the right direction.
John

John,

I created a report with forms that makes an automated report.

Moroformat

John,

I am sending you the one i created to see if there is something else I can do.

Moroformat

Hi moroformat
I'm glad that the initial effort meets with your approval. The user interface is quite weak but I'll look at that. Since you feel I'm on the right track, I'll move ahead with the "Detail Report". At this point, I don't have any questions but I'm certain some will come up.

The "Detailed Report" will involve a lot of code just for the formatting. I'm concerned about the file size. We'll see how it progresses. Hope you have a ton of memory available .

I'll get back to you in a couple of days. John

Hi moroformat
I'm stuck on an issue with the "Detail Report". The issue is how many styles are we potentially dealing with. In your sample report, you show 4 being:

Cap Sleeve
Perfect Tank
Perfect Tee
Camisole

Are there more or is this it? If this is it, it makes life a whole lot easier; if not, please post a complete list. You may like the new "User Interface" I've designed.
John

Moroformat
If I add Perfect V and Cardigan to the Styles List and a Show has no sales for these two styles (reflects 0.00) will that be an issue? Not certain if this solves my problem but is this an OK approach?
John

Is "LS" also a style?

In breaking down the styles I now have these:

Styles
Cap Sleeve
Perfect Tee
Perfect Tank
Perfect V
LS
Camisole
Cardigan

Is this correct?
John

Hi moroformat

Wanted to give you an update and let you know I've not yet given up.
I've had the "Detail Report" surrounded several times the last couple of days but keep running into situations that need "work around's". With a complex project such as this, "work around's" are not such a good idea; they always come back to bite you in the butt. So, I'm trying best I can to "standardize" things.
I think the "Month Summary" report is ready for your testing if you wish to do that. If so, let me know and I'll break the file apart and send just that part to you. This is what I posted before but I've changed the code and interface considerably. It really needs tested on other data files for consistency.
John

Hi moroformat
I've got this pretty well surrounded. I've beat this up pretty well and have most of the kinks worked out. I want to spent a little more time on it to clean up some issues I don't like but I also want you to look at it to see what changes you'd like made.
Let me know what issues you have.
John

Hi moroformat
I took most of the day off from our project; thought I'd sit down before dinner and just play with the Daily Summary to see if I could break it. I noticed right away that I've duplicated some information on the report. I'll fix that but, I'd still like you to look at it to see if there are any changes you'd like to make.
John

Hi moroformat
The attached seems to work without a hitch on all the test data. I'll wait to hear from you.
John