Option Explicit 'All variables must be declared, otherwise error messages will appear
Option Base 1 'Important, the index of numbers starts at 1
Private Sub CommandButton5_Click()
Dim i As Integer, j As Integer, uprbnd As Integer, cls As Integer
Dim rw As Long, rws As Long
Dim arrSht, arrWFIT, arrEIT, arrBIT, arrMIT, arrAll, arrPIT, arrSIT
Dim DW As Worksheet, WFIT As Worksheet, EIT As Worksheet, BIT As Worksheet, MIT As Worksheet, PIT As Worksheet, SIT As Worksheet
Application.ScreenUpdating = False
'Worksheets
Set DW = Worksheets("DailyWorksheet") 'Source
Set WFIT = Worksheets("WorkForceInformationTable") 'Target table
Set EIT = Worksheets("EquipmentInformationTable") 'Target table
Set BIT = Worksheets("BidItemInformationTable") 'Target table
Set MIT = Worksheets("MaterialInformationTable") 'Target table
Set PIT = Worksheets("ProjectInformationTable") 'Target table
Set SIT = Worksheets("SummaryInformationTable") 'Target table
'Arrays
arrSht = Array(WFIT, EIT, BIT, MIT)
arrWFIT = Array("C", "B", "A", "V", "W", "X")
arrEIT = Array("G", "F", "Y", "Z", "AA")
arrBIT = Array("A", "B", "D", "E", "V", "W", "X")
arrMIT = Array("G", "F", "J", "Y", "Z", "AA", "AB")
arrAll = Array(arrWFIT, arrEIT, arrBIT, arrMIT)
arrPIT = Array("C", "G", "J")
arrSIT = Array("A36", "C4", "G3", "G4")
'Contractor's Equipment Section
DW.Select
rw = DW.Cells(7, 3).End(xlDown).Row
For i = 1 To 2
With arrSht(i)
'.Select 'For testing
On Error Resume Next
rws = .ListObjects.Item(1).DataBodyRange.Rows.Count + 1
If Err.Number <> 0 Then rws = 1
On Error GoTo 0
'cls = .ListObjects.Item(1).ListColumns.Count
uprbnd = UBound(arrAll(i)) 'uprbnd = cls ... :-)
For j = 1 To uprbnd
.Cells(rws, j).Offset(1, 0).Resize(rw - 7, 1).Value = DW.Range(arrAll(i)(j) & 8 & ":" & arrAll(i)(j) & rw).Value
Next
End With
Next
'Bid Items Installed Section / Materials Used Section
rw = DW.Cells(18, 2).End(xlDown).Row
For i = 3 To 4
With arrSht(i)
'.Select 'For testing
On Error Resume Next
rws = .ListObjects.Item(1).DataBodyRange.Rows.Count + 1
If Err.Number <> 0 Then rws = 1
On Error GoTo 0
'cls = .ListObjects.Item(1).ListColumns.Count
uprbnd = UBound(arrAll(i)) 'uprbnd = cls ... :-)
For j = 1 To uprbnd
.Cells(rws, j).Offset(1, 0).Resize(rw - 18, 1).Value = DW.Range(arrAll(i)(j) & 19 & ":" & arrAll(i)(j) & rw).Value
Next
End With
Next
'Project Information Section
With PIT
'.Select 'For testing
On Error Resume Next
rws = .ListObjects.Item(1).DataBodyRange.Rows.Count + 1
If Err.Number <> 0 Then rws = 1
On Error GoTo 0
cls = 0
uprbnd = UBound(arrPIT)
For i = 1 To uprbnd
For j = 1 To 3
.Cells(rws, j + cls).Offset(1, 0).Value = DW.Range(arrPIT(i) & j + 2).Value
Next
cls = cls + 3
Next
End With
'Summary of Construction Activities Section
With SIT
'.Select 'For testing
On Error Resume Next
rws = .ListObjects.Item(1).DataBodyRange.Rows.Count + 1
If Err.Number <> 0 Then rws = 1
On Error GoTo 0
uprbnd = UBound(arrSIT)
For i = 1 To uprbnd
.Cells(rws, i).Offset(1, 0).Value = DW.Range(arrSIT(i)).Value
Next
End With
Set DW = Nothing
Set WFIT = Nothing
Set EIT = Nothing
Set BIT = Nothing
Set MIT = Nothing
Set PIT = Nothing
Set SIT = Nothing
Application.ScreenUpdating = True
End Sub
Bookmarks