Before Workbook Event, Custom Calculate a Specific Worksheet

[jeffbert]

On the workbook before save, before print, and before close events, I need to
have the following code run that is attached to a button on the tab name
"Distro". The code is in the "Distro" worksheet.

Sub Calculate_Distro_Sheet()

Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False


ActiveSheet.Calculate

'This sums the quantity going to the FS stores and the warehouse
'and puts it in the "FS_Whs_Distro" text box
'This is calculated by summing the two rows of freestanding stores
'including 8650, and then subtracting out 8650, and adding in the value
'of the WHS_Balance text box.


Dim FS_WHS_Distro_Range As Range
Dim Goods_to_Whs As Range
Dim Corp_Office As Range
Dim i As Variant
Dim h As Variant
Dim g As Variant



'The next two rows of code set variable i equal to the sum of the two rows
of FS stores.
Set FS_WHS_Distro_Range = Worksheets("Distro").Range("B55:AE55,B60:AE60")
i = (Application.WorksheetFunction.Sum(FS_WHS_Distro_Range))

'The next two rows of code find store 8650 and set variable h equal to the
quantity going to 8650
Set Goods_to_Whs = (Worksheets("Distro").Range("59:59").Find("8650",
LookIn:=xlValues).Offset(rowOffset:=1, columnOffset:=0))
h = Val(Goods_to_Whs)

'The next two rows of code find store 8600 and set variable g equal to the
quantity going to 8600
Set Corp_Office = (Worksheets("Distro").Range("59:59").Find("8600",
LookIn:=xlValues).Offset(rowOffset:=1, columnOffset:=0))
g = Val(Corp_Office)

'The next two rows of code sum the quantity going to FS stores, subtracts
out the value
'going to 8650 and 8600, and then adds in the balance remaining in the
warehouse. Text box "FS_Whs_Distro"
'is populated with this value.

FS_Whs_Distro = (i - h - g) + Val(WHS_Balance)





'This sums the quantity going to all FS, BBBY SWS, CTS SWS and 8600 and puts
it in the
'"Total_Distro_to_Stores" textbox.

Dim BBBY_SWS_Distro_Range As Range
Dim j As Variant

Set BBBY_SWS_Distro_Range =
Worksheets("Distro").Range("B28:AE28,B32:AE32,B36:AE36,B40:AE40")
j = Application.WorksheetFunction.Sum(BBBY_SWS_Distro_Range)



Dim CTS_SWS_Distro_Range As Range
Dim k As Variant

Set CTS_SWS_Distro_Range = Worksheets("Distro").Range("B47:AE47")
k = Application.WorksheetFunction.Sum(CTS_SWS_Distro_Range)

Total_Distro_to_Stores = (i - h) + j + k


'This will enter the total qty going to FS,SWS,Goods to remain in the
warehouse and 8600
'into the distro area for 8650 if Pre-distribution is selected.
'If Drop Ship is selected it makes the distribution to 8650 equal to the
Balance to remain
'in Warehouse text box.


If PO_Type.Value = "" Then
Total_SWS_FS_Whs.Value = Val(WHS_Balance) + Val(Total_Distro_to_Stores)
Exit Sub

ElseIf PO_Type.Value = "Pre-Distribution WHS" Then
Goods_to_Whs.Value = j + k + g + FS_Whs_Distro
Total_SWS_FS_Whs.Value = Val(WHS_Balance) + Val(Total_Distro_to_Stores)

ElseIf PO_Type.Value = "Drop Ship" Then
Goods_to_Whs.Value = Val(WHS_Balance)
Total_SWS_FS_Whs.Value = Val(WHS_Balance) + Val(Total_Distro_to_Stores)


Else: Exit Sub



End If

Application.ScreenUpdating = True
ActiveSheet.DisplayPageBreaks = True

End Sub


The code runs fine when the corresponding button is clicked, but I cannot
figure out how to get it to run on the workbook events.

Thanks for the help

Jeff

 

[Bob Phillips]

Call Distro.Calculate_Distro_Sheet

 

[jeffbert]

Bob

Thanks for the reply. When I put your code into the workbook events, I get
an error that states the object is not defined. Any ideas?

Thanks again

Jeff

 

[Bob Phillips]

This assumes that the worksheet codename is Distro. If it is not, maybe it
is Sheet1, Sheet2 or such like, then use that.

 

[jeffbert]

The tab name is Distro, I cannot get it to work.

 

[Bob Phillips]

No the tab name, the codename. Go into the VIDE and check what the other
name on that sheet is.

 

[jeffbert]

Sorry for the delay in responding, the holiday got in the way. I do not know
what the VIDE is. Your help is greatly appreciated.