Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help Needed - Added Info
am a novice and am trying to setup 3 print Macro's in one workbook.
The work book contains 4 sheets, PRODUCTION, INVOICE, ORDER, DELIVERY NOTE. PRODUCTION is the master sheet. using VLOOKUP I have successfully put all necessary data on the other 3 sheets. I now want to put 3 different BUTTON FORM CONTROLS on the PRODUCTION sheet realting to the other 3 sheets. ie PRINT INVOICE, PRINT ORDER, PRINT DELIVERY NOTE. However I will need the data being printed to be sorted automatically as I will be hiding the sheets. The print range on each sheet is the same $B$2:$M$58. The Column to be sorted automatically before printing is Column C Row8:58. I have started the macro but have now got my self confused. What I have so far is Sub PrintInv() 'print invoices' Dim wsD As Worksheet Dim wsI As Worksheet Set wsD = Worksheets("Production") Set wsI = Worksheets("Invoice") Dim rStart As Long Dim rEnd As Long Dim r As Long End Sub I would be greatful for any help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help Needed - Added Info
Hi Gavin
The following code would be one way of doing what you want, add three buttons to sheet1 (Production) then past this code in the sheet1 module. Each button will pass a sheet name to the WkSheet variable and from there it will call the sub SortAndPrint, this should work fine providing you are sorting the same range and printing the same print area for on each sheet. Option Explicit Dim WkSheet As Worksheet Private Sub CommandButton1_Click() Set WkSheet = Worksheets("Invoice") 'set worksheet SortAndPrint 'call SortAndPrint Sub End Sub Private Sub CommandButton2_Click() Set WkSheet = Worksheets("Order") SortAndPrint End Sub Private Sub CommandButton3_Click() Set WkSheet = Worksheets("Delivery Note") SortAndPrint End Sub Sub SortAndPrint() With WkSheet 'set the worksheet to use With .Range("C8:C58") 'set the range to sort .Sort key1:=WkSheet.Range("C8"), order1:=xlAscending, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'sort the range ascending End With .PageSetup.PrintArea = "$B$2:$M$58" 'set print range .PrintOut 'print End With End Sub Hope this helps you out S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed: argument changes as more data is added | Excel Worksheet Functions | |||
How do I set up a chart that auto updates as new info is added? | Excel Discussion (Misc queries) | |||
Sum a column correctly after more info is added by sorting | Excel Worksheet Functions | |||
How do i set Excel to record the date etc of info added | Excel Worksheet Functions | |||
Inventory added sub needed | Excel Programming |