Macro Help Needed - Added Info

[Gavin]

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!

 

[Incidental]

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