this code goes in Module "Setup_Utilities". I wrote it to standardize the Date labels in the Project sheets
Option Explicit
Option Private Module 'Will not show these subs in the Excel Macro menu
Private Sub FormatDateRows()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Sheets
If Sht.Name = "MR" Or Sht.Name = "PR" Then GoTo NextSht
With Sht
.Rows(5).NumberFormat = "*MMM-yyyy"
.Rows(7).NumberFormat = "*mm/dd/yy"
.Rows(21).NumberFormat = "*mm/dd/yy"
End With
NextSht:
Next Sht
End Sub
This is as far as I have gotten in the module "MainModule". I am now working on Sub Main and it's attendant sub procedures
Option Explicit
Const mrProjectCol As Long = 1
Const mrEndPeriodCol As Long = 2
Const mrLCcol As Long = 3
Const empPmpNum As Long = 1
Const empLastCol As Long = 2
Const empFirstCol As Long = 3
Const empHoursCol As Long = 4
Const empRateCol As Long = 5
Const prProjectCol As Long = 1
Const prEndPeriodCol As Long = 2
Const prLCcol As Long = 3
'These Arrays use the above declared Constants
Dim MR_Array As Variant
Dim Emp_Array As Variant
Dim PR_Array As Variant
Dim TempSht As Worksheet
Private Sub MoveColumns()
'Copies all relevant data to Tempsht in memory
'Do in memory
'Because Sheet PR Changes every week
' Not related to Constants declared above
Dim PR_ProjectColumn As Long
Dim PR_LCColumn As Long
Dim PR_EndPeriodColumn As Long
With Sheets("PR").Rows(1)
PR_ProjectColumn = .Find("Project").Column
PR_LCColumn = .Find("LC").Column
PR_EndPeriodColumn = .Find("End Period").Column
End With
With TempSht
Sheets("MR").Range("A:B,E:E").Copy .Columns("A:A") 'Project, EndPeriod, and LC
Sheets("MR").Columns("D:I").Copy .Columns("E:E") 'Emp #, Last, First, HOURS, and RATE
Sheets("PR").Columns(PR_ProjectColumn).Copy .Range("K1")
Sheets("PR").Columns(PR_EndPeriodColumn).Copy .Range("L1")
Sheets("PR").Columns(PR_LCColumn).Copy .Range("M1")
End With
Application.CutCopyMode = False
End Sub
Sub t()
'Test stub
MoveColumns
End Sub
Private Sub MakeArrays()
With Sheets("Temp")
MR_Array = .Range("A1").CurrentRegion
Emp_Array = .Range("D1").CurrentRegion
PR_Array = .Range("J1").CurrentRegion
End With
'Now, we are done with sheet temp. Clear the memory
Set TempSht = Nothing
End Sub
Private Sub Main()
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation 'Store it for later
ViewMode = ActiveWindow.View
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
' To be written
'
'
With Application
.Calculation = CalcMode
ActiveWindow.View = ViewMode
.ScreenUpdating = TrueEnd With
End Sub