I see nothing that creates even one spreadsheet, much less two. However, you
should clean up.
Sub Format_Header()
with Range("A1:K1")
.Font.Bold = True
.Interior.ColorIndex = 15
End With
End Sub
Sub Subtotal()
Range("A1").CurrentRegion.sort.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").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() ' NO selections
Columns("A:A").ColumnWidth = 4.57
Columns("B:B").ColumnWidth = 9.15
Columns("C:C").ColumnWidth = 9.71
Columns("D:D").ColumnWidth = 17.5
Columns("E:f").ColumnWidth = 12
Columns("G:G").ColumnWidth = 16
Columns("H:H").ColumnWidth = 4.45
Columns("I:I").ColumnWidth = 25
Columns("J:k").ColumnWidth = 8
Range("A1:K20000").RowHeight = 16
End Sub
Sub Col_Headers() 'NO selections
Range("A1")= "Plant"
Range("G1")= "Cust Item No"
Range("B1")= "Ship Date"
Range("C1")= "Plant Date"
Range("H1") = "Type"
Range("K1") = "Rem Qty"
End Sub
Module 2:
Sub Macro7()
'
' Macro7 Macro
'
'
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
--
Don Guillett
SalesAid Software
"childofthe1980s" wrote in
message ...
Hello:
There is a series of VBA macros that I created. These macros are doing a
good job at displaying the correct data and in the correct format. But,
there is just one problem.
These macros are creating a duplicate spreadsheet when the macros are run.
Only one spreadsheet should be created by these macros.
You see, there is a custom button that I placed on a spreadsheet that when
clicked runs all of these macros at once and creates a spreadsheet of
data.
Below is the code for all of these macros. If anyone has any ideas as to
how to prevent this duplication, please let me know.
Thanks! The code for both of the modules for this spreadsheet is below:
Module 1:
Sub Format_Header()
'
' Format_Header Macro
'
'
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
'
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
'
'
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: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
'
'
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
'
'
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