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

[frank_m]

I need to be able to produce a printed Open Order Report Filtered By Customer and ((Open Status that is marked in column A)) and by the Promise date.

I want to be able to entering the customer name in Cell C1 and click a button to create a new sheet with the report ready for printing..

Several of the columns need to be hidden, I can handle that part of the coding and I can code the printing setup, just would be very appreciative for help with the rest.

I was thinking maybe a pivot table could do this, but I do not know and I have zero experience with that, plus I need it coded in any case.

I've attached a sample workbook with some sample data and a sample report.

Thank you much

[GTO]

Hi Frank,

No experience with pivot tables on this end ; and rather than hiding columns, I just took the data from the columns desired through an input and output array.

Due to most of the code being in a userform, see attachment for code.

Mark

[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

[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

[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]

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

[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]

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

[GTO]

I am past hours and need some sleep, but here's a simple fix after I confirmed (what I most likely knew, but blond/forgetful) as to .Keys being case insensitive in Collection, which differs from the default for Dictionary.

http://support.microsoft.com/kb/189251
http://support.microsoft.com/kb/187234

We can change sensitivity in the Dictionary, thus eliminating seperate "unique" keys for hsp vs. HSP.

[VBA]Private Function GetNames(ByVal rng As Range, ary() As Variant) As Boolean
Dim DIC As Object '<--- Scripting.Dictionary
Dim aryNames As Variant
Dim n As Long

On Error GoTo ErrExit
ary = rng.Value
Set DIC = CreateObject("Scripting.Dictionary")
DIC.CompareMode = TextCompare '<***ADDED***

For n = 1 To UBound(ary, 1)
DIC.Item(Trim(ary(n, 1))) = Empty
Next

If DIC.Exists(vbNullString) Then DIC.Remove (vbNullString) '<***ADDED***

ary = DIC.Keys
If DIC.Count > 1 Then
ary = SimpleSort(ary)
End If

GetNames = True
On Error GoTo 0 '<***ADDED and shame on me, error handling is inherited, which is
' why we didn't ID the source of the error in SimpleSort()
Exit Function
ErrExit:
MsgBox "Error in 'GetNames()'", vbInformation, vbNullString
End Function

Private Function SimpleSort(ary() As Variant) As Variant()
Dim COL As Collection
Dim i As Long
Dim n As Long
Dim bAdded As Boolean

On Error GoTo QuickNote
'// See vba help, but somewhat similar to Dictionary. I used Collection for a //
'// simple sort, as Collection has ability to add an entry in a specific place in //
'// the Collection. This will get too slow if there are a bajillion names. //
Set COL = New Collection
'// Initial placeholder. //
COL.Add "DUMMY", "DUMMY"
For i = LBound(ary) To UBound(ary)
bAdded = False
For n = 1 To COL.Count
If UCase(ary(i)) < UCase(COL.Item(n)) Then
COL.Add ary(i), CStr(ary(i)), COL.Item(n)
bAdded = True
Exit For
End If
Next

If bAdded Then
bAdded = False
Else
COL.Add ary(i), CStr(ary(i))
End If
Next

COL.Remove "DUMMY"
ReDim aryTmp(0 To COL.Count - 1)
For n = 1 To COL.Count
aryTmp(n - 1) = COL.Item(n)
Next

SimpleSort = aryTmp
On Error GoTo 0 '<***ADDED to reset error handler
Exit Function
QuickNote:
MsgBox "Error in 'SimpleSort()'", vbInformation, vbNullString '<*** to find out quicker
End Function
[/VBA]

Hopefully I'm not missing anything, but am beat so will check tonight. (Currently working nights, which pretty much *@!#*)

Hope you have a great day,

Mark

[frank_m]

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

On second thought I think we had better make the report include all customers that begin with the same letter(non-case sensitive), as that is the only reasonable way to guarentee that we never miss anything. -- In other words, I'll be ok with multiple customers on the same report, and I'll be in terrible shape with some orders that are for the same customer, different spelling, therefore missing from the report.

Sorry to have realized this so late

Your most recent code does avoid errors and runs fast enough.
\
Thanks

[GTO]

Hi Frank,

As discussed via PM, here is a version wherein the listbox is still filled with all (unique) customer names, however, the listbox is multiselect. The user can now select several customers, where let's say HMS and H.M.S. are actually the same customer, and the code will write the records to one output report.

I have tested in Excel 2000 and 2010, both in WIN/XP.

Mark

[frank_m]

Thanks Mark for all your hard work on this..

Unfortunately in trying this with my Excel 2003, I’m getting the following Error's:

'GetNames()'
Link error in 'GetNames()', report issue. (<-- this is the custom ErrorHandler message written into the code)
When I click ok
I get this error
Runtime error 91
Object variable or with block variable not set
-----------------------------------------------------------------------
When I remove your On Error GoTo ErrExit (error handler)
and try again, I get:
Run-time error '-2147319779 (8002801d):
Automation Error
Library not registered
---------------------------------------------------------------------------------------------------

Being this is developed in Excel 2010 and tested only in 2010 and 2000, and I am needing to use it in Excel 2003, it would be great if some other members here that have Excel 2003 would be so kind as to download and try running Marks attached workbook.

I'd be very grateful for your feedback as to whether or not it runs error free for you.

Thank you much

[frank_m]

HI shrivallabha,

Sorry I took so long to try your code. It works quite well. Exactly what I had asked for originally, less a few tweaks to name the sheet by customer name + 01, 02, 03 etc, to handle when the report is run multiple times for the same customer, and a hidden sheet template with a delete sheet commandbutton.<-- That I likely can figure out how to adapt from Marks code.

For now I'm going to stick with working with Mark towards getting rid of the errors I'm experiencing in his workbook, being that I like his approach that uses a Userform with listbox filled with unique names and option buttons. -- But it's indeed comforting knowing that I have another way to go, if I need to.

[frank_m]

I would be very appreciative if some other member/'s here that have Excel 2003 would be so kind as to download and try running Marks attached workbook in Post#16
I'd be very grateful for your feedback as to whether or not it runs error free for you.

Thank you much

It's relatively simple to test. If you have Excel 2003, just open the workbook that is attached in Post#16, and click the Run Report Button. - If the userform opens that means you are not getting the Error's that I am.

Eagerly looking for a willing participant.

Thanks in advance - Frank

[shrivallabha]

I have 2007 and the code works fine. Looking at the situation, I have a suggestion. You can make Mark's UserForm multipage. One Page will be for report creation and the other will have options to delete.

I am attaching my concept in a separate workbook.

Edit: Bumblybee seems to be using 2003 who you are helping @ the moment!