C
childofthe1980s
Hello:
We have a few macros embedded in a .xlt (Excel template) file for a client
of ours.
So the client would not have to individually run each of the five macros on
her workstation, we created a custom button (smiley face) that runs one of
the macros. And, this one macro runs the others as well. The name of this
macro is "Subtotal".
For some reason, when the client hits this custom button inside the template
file, two spreadsheets are generated rather than just one.
Why is this? Do macros in Excel template files just naturally generate
duplicate spreadsheets like this?
Below is the code. If you have any ideas on how to prevent this
duplications of spreadsheets, that would be great!
Module 1:
Sub Format_Header()
'
' Format_Header Macro
' Macro recorded 6/14/2006 to format column header row
Range("A1:K1").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A1").Select
End Sub
Sub Subtotal()
'
' Subtotal Macro
' Macro recorded 6/14/2006 to add subtotals to each break in Ship Date
'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(4), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Format_Header
Col_Width
Col_Headers
End Sub
Sub Col_Width()
'
' Col_Width Macro
' Macro recorded 6/14/2006 to set column widths and row height
'
Columns("A:A").Select
Selection.ColumnWidth = 4.57
Columns("B:B").Select
Selection.ColumnWidth = 9.15
Columns("C:C").Select
Selection.ColumnWidth = 9.71
Columns("D").Select
Selection.ColumnWidth = 17.5
Columns("E:E").Select
Selection.ColumnWidth = 12
Columns("F:F").Select
Selection.ColumnWidth = 12
Columns("G:G").Select
Selection.ColumnWidth = 16
Columns("H:H").Select
Selection.ColumnWidth = 4.45
Columns("I:I").Select
Selection.ColumnWidth = 25
Columns("J:J").Select
Selection.ColumnWidth = 8
Columns("K:K").Select
Selection.ColumnWidth = 8
Range("A1:K20000").Select
Range("A4").Activate
Selection.RowHeight = 16
End Sub
Sub Col_Headers()
'
' Col_Headers Macro
' Macro recorded 6/14/2006 to set column headers
'
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plant"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Cust Item No"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Ship Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Plant Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Rem Qty"
Range("A1").Select
End Sub
Module 2:
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 6/14/2006
'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select
End Sub
Thanks, again!
childofthe1980s
We have a few macros embedded in a .xlt (Excel template) file for a client
of ours.
So the client would not have to individually run each of the five macros on
her workstation, we created a custom button (smiley face) that runs one of
the macros. And, this one macro runs the others as well. The name of this
macro is "Subtotal".
For some reason, when the client hits this custom button inside the template
file, two spreadsheets are generated rather than just one.
Why is this? Do macros in Excel template files just naturally generate
duplicate spreadsheets like this?
Below is the code. If you have any ideas on how to prevent this
duplications of spreadsheets, that would be great!
Module 1:
Sub Format_Header()
'
' Format_Header Macro
' Macro recorded 6/14/2006 to format column header row
Range("A1:K1").Select
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A1").Select
End Sub
Sub Subtotal()
'
' Subtotal Macro
' Macro recorded 6/14/2006 to add subtotals to each break in Ship Date
'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select
Selection.Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(4), _
Replace:=True, PageBreaks:=True, SummaryBelowData:=True
Selection.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Format_Header
Col_Width
Col_Headers
End Sub
Sub Col_Width()
'
' Col_Width Macro
' Macro recorded 6/14/2006 to set column widths and row height
'
Columns("A:A").Select
Selection.ColumnWidth = 4.57
Columns("B:B").Select
Selection.ColumnWidth = 9.15
Columns("C:C").Select
Selection.ColumnWidth = 9.71
Columns("D").Select
Selection.ColumnWidth = 17.5
Columns("E:E").Select
Selection.ColumnWidth = 12
Columns("F:F").Select
Selection.ColumnWidth = 12
Columns("G:G").Select
Selection.ColumnWidth = 16
Columns("H:H").Select
Selection.ColumnWidth = 4.45
Columns("I:I").Select
Selection.ColumnWidth = 25
Columns("J:J").Select
Selection.ColumnWidth = 8
Columns("K:K").Select
Selection.ColumnWidth = 8
Range("A1:K20000").Select
Range("A4").Activate
Selection.RowHeight = 16
End Sub
Sub Col_Headers()
'
' Col_Headers Macro
' Macro recorded 6/14/2006 to set column headers
'
Range("A1").Select
ActiveCell.FormulaR1C1 = "Plant"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Cust Item No"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Ship Date"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Plant Date"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Type"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Rem Qty"
Range("A1").Select
End Sub
Module 2:
Sub Macro7()
'
' Macro7 Macro
' Macro recorded 6/14/2006
'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Range("A1").Select
End Sub
Thanks, again!
childofthe1980s