CSI_William
New Member
- Joined
- Dec 21, 2020
- Messages
- 2
- Office Version
- 2019
- Platform
- Windows
I have a fairly basic macro running on a customer computer for reporting. When the macro finished Excel goes to the background rather than showing the finished product on the screen. The macro was written in Excel 2019 and is running on Excel 2016.
VBA Code:
Sub Complete()
'
' Macro1 Macro
'
'
Sheets.Add.Name = "Daily Report"
Call Macro2
Call Macro3
Call Macro4
Call Macro5
End Sub
Sub Macro2()
'
' Macro2 Macro
'
'
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Daily Report"
Range("A3").Select
ActiveCell.FormulaR1C1 = "Collinsville WTP"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Well 1"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Well 2"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Well 3"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Service Pump 1"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Service Pump 2"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Martin WTP"
Rows("4:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A4").Select
ActiveCell.FormulaR1C1 = "Device"
Range("B4").Select
ActiveCell.FormulaR1C1 = "Starts"
Range("C4").Select
ActiveCell.FormulaR1C1 = "Runtime"
Range("D4").Select
ActiveCell.FormulaR1C1 = "Total Flow (Kgals)"
Range("A4:E4").Select
Selection.Copy
Range("A13").Select
ActiveSheet.Paste
Range("A14").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Well 4"
Range("A16").Select
ActiveCell.FormulaR1C1 = "Service Pump 1"
Range("A17").Select
ActiveCell.FormulaR1C1 = "Service Pump 2"
Range("A3:D3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A12:D12").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Columns("A:A").EntireColumn.AutoFit
End Sub
Sub Macro3()
'
' Macro3 Macro
'
'
Range("A1:B1").Select
Selection.Font.Bold = True
Range("A3:E4").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A8:D8").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A15:D15").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A12:D13").Select
Selection.Font.Bold = True
Range("A3:D10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A12:D17").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A3:D3").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A12:D12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A3:D10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A12:D17").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub
Sub Macro4()
'
' Macro4 Macro
'
'
Sheets("Sheet1").Select
Range("C2:W3").Select
Selection.Copy
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A8").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Columns("A:A").EntireColumn.AutoFit
Range("A36").Select
Sheets("Daily Report").Select
Range("B5").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Sheet1!R[3]C"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C"
Range("B7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[4]C[-1]"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C[-1]"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[6]C[-1]"
Range("C5:C7").Select
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.00000"
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
Selection.NumberFormat = "0.00"
Range("B9").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[5]C"
Range("B10").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[6]C[-1]"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[-1]"
Range("C9:C10").Select
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
Selection.NumberFormat = "0.00"
Range("B14").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[6]C"
Range("C14").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C[-1]"
Range("C14").Select
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
Selection.NumberFormat = "0.00"
Range("B16").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C"
Range("B17").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C"
Range("C16").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[8]C[-1]"
Range("C17").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[10]C[-1]"
Range("C16:C17").Select
Selection.NumberFormat = "0.0000"
Selection.NumberFormat = "0.000"
Selection.NumberFormat = "0.00"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[7]C[-2]"
Range("D10").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C[-2]"
Range("D16").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C[-2]"
Range("D17").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[11]C[-2]"
Range("D18").Select
End Sub
Sub Macro5()
'
' Macro5 Macro
'
'
Range("B1:C1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("B1:C1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[2]C[-1]"
Range("B2").Select
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Daily Report").Select
End Sub