Hi, Charles T!
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Automatically Update Infomation In Cells When New Rows or Columns Are Added - Employee_Hours (for Charles T at chandoo.org).xlsm
It uses many dynamic named ranges for make formulas and ranges handling easy, basically this:
Single ranges:
MonthEmployeeHourCell: C5
StartDateCell: C8
MonthNoCell: C9
EmployeeNoCell: C10
TitleHeaderCell: B14 (previous row cell of EmployeeList)
TitleFooterCell: B27 (next row cell of EmployeeList)
TitleSeparator1Cell: C14 (previous column cell of MonthPercentageList)
TitleSeparator2Cell: L14 (next column cell of MonthPercentageList)
List ranges:
EmployeeList: B15:B26
=DESREF(Sheet1!$B$15;;;FILA(TotalFooterCell)-FILA(TitleHeaderCell)-1;1) -----> in english: =OFFSET(Sheet1!$B$15,,,ROW(TotalFooterCell)-ROW(TitleHeaderCell)-1,1)
MonthPercdentageList: D14:K14
=DESREF(Sheet1!$D$14;;;1;COLUMNA(TitleSeparator2Cell)-COLUMNA(TitleSeparator1Cell)-1) -----> in english: =OFFSET(Sheet1!$D$14,,,1,COLUMN(TitleSeparator2Cell)-COLUMN(TitleSeparator1Cell)-1)
MonthHourList: M14:T14
=DESREF(MonthPercentageList;0;COLUMNAS(MonthPercentageList)+1;; ) -----> in english: =OFFSET(MonthPercentageList,0,COLUMNS(MonthPercentageList)+1,,)
Changed the format of starting date from free text (string) to a valid date, formatted as "mmmm yyyy" (unquoted), so as to handle the formulas for MonthPercentageList (from where the yellow background was removed as it isn't anymore input but calculated) as:
D14: =FIN.MES(StartDateCell;COLUMNA()-COLUMNA(MonthPercentageList)) -----> in english:
=EOMONTH(StartDateCell,COLUMN()-COLUMN(MonthPercentageList))
and MonthHourlist as:
M14: =D14
This is the code for the worksheet Sheet1 class module:
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' constants
Const ksEmployeeNo = "EmployeeNoCell"
Const ksEmployee = "EmployeeList"
Const ksMonthNo = "MonthNoCell"
Const ksMonthPercentage = "MonthPercentageList"
Const ksMonthHours = "MonthHourList"
' declarations
Dim rngENo As Range, rngE As Range, rngMNo As Range, rngMP As Range, rngMH As Range
Dim iNumber As Integer
' start
With Application
.EnableEvents = False
' .ScreenUpdating = False
End With
Set rngENo = Range(ksEmployeeNo)
Set rngMNo = Range(ksMonthNo)
If Application.Intersect(Target, rngENo) Is Nothing And _
Application.Intersect(Target, rngMNo) Is Nothing Then GoTo Worksheet_Change_Exit
' process
Set rngE = Range(ksEmployee)
Set rngMP = Range(ksMonthPercentage)
Set rngMH = Range(ksMonthHours)
' employee
iNumber = rngENo.Cells(1, 1).Value - rngE.Rows.Count
AddDeleteRowsColumns ActiveSheet, rngE, iNumber, 0
' month percentage
iNumber = rngMNo.Cells(1, 1).Value - rngMP.Columns.Count
AddDeleteRowsColumns ActiveSheet, rngMP, 0, iNumber
' month hour
iNumber = rngMNo.Cells(1, 1).Value - rngMH.Columns.Count
AddDeleteRowsColumns ActiveSheet, rngMH, 0, iNumber
'end
Set rngMH = Nothing
Set rngMP = Nothing
Set rngE = Nothing
Worksheet_Change_Exit:
Set rngMNo = Nothing
Set rngENo = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
Beep
End Sub
Private Sub AddDeleteRowsColumns(poWorksheet As Worksheet, poRange As Range, _
piRows As Integer, piColumns As Integer)
' constants
' declarations
' start
' process
With poWorksheet
If piRows <> 0 Then
Select Case piRows
Case Is < 0
Range( _
.Rows(poRange.Row + poRange.Rows.Count + piRows), _
.Rows(poRange.Row + poRange.Rows.Count - 1)).EntireRow.Delete _
xlShiftUp
Case Is > 0
poRange.Rows(poRange.Rows.Count).EntireRow.Copy
.Range( _
.Rows(poRange.Row + poRange.Rows.Count), _
.Rows(poRange.Row + poRange.Rows.Count + piRows - 1)).EntireRow.Insert _
xlShiftDown, xlFormatFromLeftOrAbove
End Select
ElseIf piColumns <> 0 Then
Select Case piColumns
Case Is < 0
Range( _
.Columns(poRange.Column + poRange.Columns.Count + piColumns), _
.Columns(poRange.Column + poRange.Columns.Count - 1)).EntireColumn.Delete _
xlShiftToLeft
Case Is > 0
poRange.Columns(poRange.Columns.Count).EntireColumn.Copy
.Range( _
.Columns(poRange.Column + poRange.Columns.Count), _
.Columns(poRange.Column + poRange.Columns.Count + piColumns - 1)).EntireColumn.Insert _
xlShiftToRight, xlFormatFromLeftOrAbove
End Select
Application.CutCopyMode = False
End If
End With
' end
End Sub
The core is the procedure AddDeleteRowsColumns which does the dirty job.
Just advise if any issue.
Regards!
PS: I left the shape of Calculate button since it's not necessary as the code is triggered by the worksheet change event for cells C9:C10 (MonthNoCell & EmployeeNoCell). I also removed module Module1 as it was empty, leaving module Module2 as it was (even if not used) as it was your recorded code.
