Need to generate Open Order Report Filtered By Customer and Due date

[shrivallabha]

Frank Hi,

Here's an autofilter based approach. What I will suggest is keeping one sheet as "Template" so you don't have to do formatting through VBA. If you don't want others to see the template then you may hide it (veryhidden). Make it visible during processing and then hide it again.

Looking at the inputting, you can add Data Validation (Open / Closed) in Column A to avoid wrong inputs.

At this point, the code doesn't create a new sheet for the report and there's no error handling. But it can be done once you decide your approach. I am attaching the revised workbook. The code is as below:
[vba]Public Sub GetMeOpenReport()
'Same Variable can be used as procedures don't overlap
Dim lLR As Long, lLR2 As Long
Application.ScreenUpdating = False
'Filtering the data
With Sheets("Data")
.AutoFilterMode = False
lLR = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:V" & lLR).AutoFilter Field:=1, Criteria1:="Open"
.Range("A2:V" & lLR).AutoFilter Field:=3, Criteria1:=.Range("C1").Value
Sheets("Template").Range("A2:J" & Rows.Count).ClearContents
.Range("A3:O" & lLR).SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Template").Range("A2")
.AutoFilterMode = False
End With
'Deleting unwanted data
With Sheets("Template") 'hard coded as format will remain same
lLR2 = .Range("A" & Rows.Count).End(xlUp).Row
.Range("N2:N" & lLR2).Delete shift:=xlToLeft
.Range("J2:J" & lLR2).Delete shift:=xlToLeft
.Range("I2:I" & lLR2).Delete shift:=xlToLeft
.Range("F2:F" & lLR2).Delete shift:=xlToLeft
.Range("D2" & lLR2).Delete shift:=xlToLeft
End With
Application.ScreenUpdating = True
End Sub
[/vba]

EDIT: I had tried my hand at pivot table through VBA http://www.vbaexpress.com/forum/showthread.php?t=38347

[frank_m]

Edit: before wasting your time reading any of this, Please refer to Edit comments near bottom of this post .

Hi Mark,

Thank you so much for putting this much time and creativity into your solution, towards making it even more user friendly than what I asked for.

Will this run in Excel 2003 ?

I'm getting this error.
Error Message: Can’t find project or library

Code stops at Trim in this line
Application.Match(Trim(ActiveCell.Value), .lstCustomers.List, 0) - 1

So I tried removing Trim

ran it gain and it stops and highlights in blueTrim at the next Trim
DIC.Item(Trim(ary(n, 1))) = Empty

Remove that Trim and ran it again.
it stope at UCase
If UCase(ary(i)) < UCase(COL.Item(n)) Then

removed UCase
and tried again
Now it stops at ReDim aryTmp(0 To COL.Count - 1)

Where I have underlined is highlighted in dark blue , like what you get when you select text with the mouse

in the vbe references box it says MISSING VBHTMLMaker.xla Can we do something different so that we will not need an ADDIN ?
I ask that because this will be distributed to several users, using both Excel 2003, 2007 and 2010 and prefer that deployment be effortless.

Edit: I figured it out. The reference I described is not needed. I unchecked it, and it runs fine.. I'm guessing you had it in there for some other project.


Code and userform are very cool. - Print setup and sheet layout, are exactly as I wanted. -- Thanks a million Mark

I'll test more tomorrow evening.

--------------------------------------------------------------
[]

Hi shrivallabha,

Thanks for your code. I have to sleep now, but I will try in out in about 12 to 16 hours from now.

[]

[frank_m]

The only thing that I see so far that I will want to add to that is a command button on the sheet style report that allows the user to easily delete it. Otherwise they will be too lazy to delete manualy and we'll end up with thousands of sheets. I know how to add the button and I'll google my way through learning how to add code behind the button programicly.

Also I'll add a save as name and date to the workbook report. That I know I can manage to code myself, without too much struggle.

As I commented in my last post I need to test what you gave me under a full set of real data, 30,000 rows. About 100 customers, but i have a feeling that it will work fine.

Thanks again Mark

[shrivallabha]

Try something on these lines (when user presses the cancel button to exit UF)
[vba]
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim vbResult As VbMsgBoxResult
Dim i As Integer
vbResult = MsgBox("Do you want to delete reports generated?", vbYesNo, "Delete Report")
Select Case vbResult
Case 6
For i = Sheets.Count To 2 Step -1
Application.DisplayAlerts = False
Sheets(i).Delete
Application.DisplayAlerts = True
Next i
Case 7
'Do nothing
End Select
End Sub[/vba]

[frank_m]

Thanks shrivallabha --- I'll try that in a few minutes


Hi Mark,

I'm still awake. Playing with this is exciting.. please see attached workbook that has about 17,00 name entries with thousands of duplicates

When I run that I get the error
Link Error in 'GetNames(), report issue

I'm testing with Excel 2003

[GTO]

Thanks shrivallabha --- I'll try that in a few minutes


Hi Mark,

I'm still awake. Playing with this is exciting.. please see attached workbook that has about 17,00 name entries with thousands of duplicates

When I run that I get the error
Link Error in 'GetNames(), report issue

I'm testing with Excel 2003

I found my oversight. Collection evidently reads keys in a non case sensitive manner, but I want to check something first.

Specific to your customer names, if we have a listing of HSP and a listing of hsp, are we assued these are the same company, or different?

Mark

[frank_m]

if we have a listing of HSP and a listing of hsp, are we assued these are the same company, or different?

Mark

HSP and hsp are the same, and anything like that would always be the same...Hsp for example. Even H.S.P. and H.s.p. all 4 spellings should be treated as being the same name, but I don't expect you to deal with that. If I ever find issues with things like that I'll use code that removes all periods, and eventualy I will add cell validation that won't allow periods during data entry.

Have a nice day there Mark

[frank_m]

Try something on these lines (when user presses the cancel button to exit UF)
[/vba]

Nice thought but it won't be practicle as the user most of the time needs to hang on to the report for a couple days.

Your earlier idea of using a hidden template sheet, also Mark mentioned that, I think that will work well.

Thanks

[GTO]

The only thing that I see so far that I will want to add to that is a command button on the sheet style report that allows the user to easily delete it. Otherwise they will be too lazy to delete manualy and we'll end up with thousands of sheets. I know how to add the button and I'll google my way through learning how to add code behind the button programicly.

Just a suggestion, but rather than programmatically adding the button and code (or at least the button and specifying the macro it points at, if a forms type button), I would think about using a "template" sheet. That way the button and underlying code are saved and don't need created. The "template" sheet could remain veryhidden and changed to hidden for a moment and copied programmatically, either to the new wb, or within the original wb.

As I commented in my last post I need to test what you gave me under a full set of real data, 30,000 rows. About 100 customers, but i have a feeling that it will work fine.

Thanks again Mark

Ahhh... I was curious how many customers 30k+ rows meant. I think a hundred or thereabouts should be fine, even for the rudimentary sort. Leastwise I hope

You are most welcome of course

Mark

[frank_m]

I would think about using a "template" sheet. That way the button and underlying code are saved and don't need created. The "template" sheet could remain veryhidden and changed to hidden for a moment and copied programmatically

Yes, much better thinking

In fact shrivallabha ,mentioned that same thing earlier, it just did not sink in . Probably because I'm sleepy.

I was posting again at about the same time you were. Please refer to Post#8, to see an error I'm now getting..
New sample workbook attached there with 18,000 rows

Thanks

[GTO]

I'm getting this error.
Error Message: Can’t find project or library

...in the vbe references box it says MISSING VBHTMLMaker.xla

I ask that because this will be distributed to several users, using both Excel 2003, 2007 and 2010 and prefer that deployment be effortless.

Edit: I figured it out. The reference I described is not needed. I unchecked it, and it runs fine.. I'm guessing you had it in there for some other project.

Well its a mystery to me as to why it glommed onto any add-in, much less that particular one, as I'm sure I have a couple of add-ins that you likely do not. The only thing I can think of is if I used the add-in whilst in the workbook and this somehow 'registered' it as being needed. I checked references though, and by golly it certainly is referenced.

I'm glad you found and resolved the issue so quickly.

ACK! Please re-check references and if (I think it will be) 'Microsoft Scripting Runtime' is referenced, deselect this as well. This library is the one for Dictionary, but is uneeded, as I wrote it late-bound.

As to running in different versions, I wrote it in Excel2000 and tested in 2010. I do not believe there is anything in the code that will hiccup for any version from at least 2000.

It will be interesting to see how the sort does against a full listing of customers

Mark