Before Workbook Event, Custom Calculate a Specific Worksheet

July 01st, 2008 - 03:59 pm ET by jeffbert | Report spam

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

Follow the discussion Replies

6 replies

Make a reply

Replies

#1 Bob Phillips

July 01st, 2008 - 05:10 pm ET | Report spam

Call Distro.Calculate_Distro_Sheet

__________________________________
HTH

Bob

"jeffbert" wrote in message
news:

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

Reply to this message

#2 jeffbert

July 01st, 2008 - 05:28 pm ET | Report spam

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" wrote:

Call Distro.Calculate_Distro_Sheet

__________________________________
HTH

Bob

"jeffbert" wrote in message
news:
> 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

Reply to this message

#3 Bob Phillips

July 01st, 2008 - 05:40 pm ET | Report spam

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

__________________________________
HTH

Bob

"jeffbert" wrote in message
news:

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" wrote:

Call Distro.Calculate_Distro_Sheet

__________________________________
HTH

Bob

"jeffbert" wrote in message
news:
> 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

Reply to this message

#4 jeffbert

July 01st, 2008 - 06:09 pm ET | Report spam

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

"Bob Phillips" wrote:

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

__________________________________
HTH

Bob

"jeffbert" wrote in message
news:
> 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" wrote:
>
>> Call Distro.Calculate_Distro_Sheet
>>
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "jeffbert" wrote in message
>> news:
>> > 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
>>
>>
>>

Reply to this message

#5 Bob Phillips

July 01st, 2008 - 06:59 pm ET | Report spam

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

__________________________________
HTH

Bob

"jeffbert" wrote in message
news:

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

"Bob Phillips" wrote:

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

__________________________________
HTH

Bob

"jeffbert" wrote in message
news:
> 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" wrote:
>
>> Call Distro.Calculate_Distro_Sheet
>>
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "jeffbert" wrote in message
>> news:
>> > 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
>>
>>
>>

Reply to this message

Create a new topicNext page

Make a reply

Make your own search

Before Workbook Event, Custom Calculate a Specific Worksheet

Similar topics

Replies