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

Call Distro.Calculate_Distro_Sheet

--
__________________________________
HTH

Bob

"jeffbert" <(E-Mail Removed)> wrote in message
news:3C1F40E9-1639-4D33-AD89-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:3C1F40E9-1639-4D33-AD89-(E-Mail Removed)...
> > 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:9620DE9E-F6A1-4130-8545-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:3C1F40E9-1639-4D33-AD89-(E-Mail Removed)...
>> > 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
> news:9620DE9E-F6A1-4130-8545-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:3C1F40E9-1639-4D33-AD89-(E-Mail Removed)...
> >> > 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
> >>
> >>
> >>
>
>
>

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

--
__________________________________
HTH

Bob

"jeffbert" <(E-Mail Removed)> wrote in message
news:55AF08A6-2B1C-47BE-9695-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:9620DE9E-F6A1-4130-8545-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:3C1F40E9-1639-4D33-AD89-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>

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.

"Bob Phillips" wrote:

> No the tab name, the codename. Go into the VIDE and check what the other
> name on that sheet is.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "jeffbert" <(E-Mail Removed)> wrote in message
> news:55AF08A6-2B1C-47BE-9695-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:9620DE9E-F6A1-4130-8545-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:3C1F40E9-1639-4D33-AD89-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>