Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking for Solutions
You would present a list of suppliers in a combobox or listbox on your
userform See John Walkenach's page on this: http://www.j-walk.com/ss/excel/tips/tip47.htm Filling a ListBox With Unique Items Once the user has made a selection from this list, you could use the Advance filter to place the information on another page Sub CopySupplier() Dim shSrc as Worksheet Dim sh as Worksheet Dim rng as Range, cell as Range Dim res as Variant set ShSrc = Worksheets("Data") Worksheets.Add set sh = Activesheet 'Set the criteria range to match supplier sh.Range("N1:N2").Value "=" & Userform5.Listbox1.Value Sheets("Sheet5").Select Sheets("Data").Range("A1").CurrentRegion.Columns(" A:F").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=Sheets("Data").Range("N1:N2"), _ CopyToRange:=Sh.Range("A1"), _ Unique:=False with sh set rng = .Cells(rows.count,1).End(xlup)(2) for each cell in rng.Resize(1,12) if isnumeric(cell.offset(-1,0)) then cell.FormulaR1C1 = "=Sum(R2C:R[-1]C)" end if next res = Msgbox("print Sheets", vbYesNo) if res = vbYes then sh.Printout sh.Delete end if End With End Sub You could have the option to print as a choice in the userform and just check it as part of your code. It is unclear what you want to happen if the option to print is not selected. Seems like deleting the form at the point would be a waste The above code is untested and may have typos, but should represent a basic approach. -- Regards, Tom Ogilvy "steve_doc" wrote in message ... Hi again all I am looking for a solution to a specific Excel application that I am Attempting to write. I will try to explain, forgive me if it is not clear to see. In its simplist form it is a Journal of entries, with Sales and Purchases. The Workbook has been set up in the following way. Sheet 1 = Data (Base data to be used and referenced) Sheet 2 = Company Accounts (Col A-F = Purcase entries)(G-L = Sales entries) 1 entry per row starting with the Customer/Supplier Name Imput to all these Sheets is done by UserForms, which have been set up and are working correctly. What i would like to do is with a form, be able to list all Purchase enties by 1 supplier, ie on selection of that "client" the form would: 1.Open a new Sheet with the "clients" Name 2.List all transaction relevant to that supplier 3.Sub total the account 4.Have the option to print said summary 5.Either Clear all data, from sheet, or delete sheet. I looked at the option to have a seperate sheet per client, but the size of the Workbook, could be limiting I have very little knowledge of VBA, although I am learning(slowly), so if some explanations of how, why, and what would be greatly appreciated Kind Regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula with many solutions. | New Users to Excel | |||
Spreadsheet Solutions | Excel Programming | |||
Using VBA: Trying to write new solutions ONLY | Excel Programming | |||
VBA to write Unique Solutions ONLY? | Excel Programming | |||
Looking for Solutions | Excel Programming |