Sub Summarize_mod()
Dim btn As Long 'bottom row
Dim Rght As Long 'far right column
Dim LastRow As Long
Dim lngPassed As Long
On Error GoTo Summarize_mod_Error
'###Turn the flickering of the screen off
Application.ScreenUpdating = False
'###Variable for Error-Handling to show what to look at when an error occurs
lngPassed = 0
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'CLEAR ALL DATA CLEAR ALL DATA CLEAR ALL DATA
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'###Using a with-clause enables us to work on any range of the sheet without activating that sheet
With Sheets("SUMMARY SHEET")
' If AutoFiler is on, Turn Off '????????????
If .AutoFilterMode Then
.AutoFilterMode = False
End If
' Clear the Summary Sheet items, except headers
'###Instead of using the macro.recorder syntax we build a range by using Cells inside the range to inform which should be used
.Range(.Range("A2"), .Range("A2").SpecialCells(xlLastCell)).Delete Shift:=xlUp
End With
lngPassed = 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'COPY THE ANNUAL REPORTS DATA COPY THE ANNUAL REPORTS DATA
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''
'COPY THE STATES DATA
''''''''''''''''''''''''''''
' Select Annual Reports & Copy States
With Sheets("Annual Report")
'Find the last row in column A
'###shorten the code
btn = .Range("A6000").End(xlUp).Row
'Activate cell A
'###shorten the code
Rght = .Range("A1").Column
'paste the values over
'###we search for the last used row on Sheet SUMMARY SHEET, go down a row, build an area of rows from the bottom row - 3
'###we havbe to add 1 to get the proper number of rows, and 1 column
Sheets("SUMMARY SHEET").Range("A6000").End(xlUp).Offset(1, 0).Resize(btn - 2, 1).Value = _
.Range(.Cells(3, Rght), .Cells(btn, Rght)).Value
End With
lngPassed = 2
''''''''''''''''''''
'COPY THE DUE DATES
''''''''''''''''''''
' Select Annual Reports & Copy Due Daates
With Sheets("Annual Report")
'Find the last row in column A
btn = .Range("A6000").End(xlUp).Row
'Activate cell J
Rght = .Range("J1").Column
'paste the values over
Sheets("SUMMARY SHEET").Range("B6000").End(xlUp).Offset(1, 0).Resize(btn - 2, 1).Value = _
.Range(.Cells(3, Rght), .Cells(btn, Rght)).Value
End With
lngPassed = 3
''''''''''''''''''''
'COPY THE FILE TYPES
''''''''''''''''''''
With Sheets("SUMMARY SHEET")
.Range("C2").Value = Sheets("DO NOT DELETE").Range("A7").Value
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("C2").Copy .Range("C3:C" & LastRow)
End With
lngPassed = 4
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'COPY THE FRANCHISE REPORTS DATA COPY THE FRANCHISE REPORTS DATA
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''
'COPY THE STATES DATA
''''''''''''''''''''''''''''
' Select Annual Reports & Copy States
With Sheets("Franchise")
'Find the last row in column A
btn = .Range("A6000").End(xlUp).Row
'Activate cell A
Rght = .Range("A1").Column
'Move down to row 3 in column A
Sheets("SUMMARY SHEET").Range("A6000").End(xlUp).Offset(1, 0).Resize(btn - 2, 1).Value = _
.Range(Cells(3, Rght), Cells(btn, Rght)).Value
End With
lngPassed = 5
''''''''''''''''''''
'COPY THE DUE DATES
''''''''''''''''''''
' Select Annual Reports & Copy Due Daates
With Sheets("Franchise")
'Find the last row in column A
btn = .Range("A6000").End(xlUp).Row
'Activate cell J
Rght = .Range("J1").Column
'paste the values over
Sheets("SUMMARY SHEET").Range("B6000").End(xlUp).Offset(1, 0).Resize(btn - 2, 1).Value = _
.Range(.Cells(3, Rght), .Cells(btn, Rght)).Value
End With
lngPassed = 6
''''''''''''''''''''
'COPY THE FILE TYPES
''''''''''''''''''''
Sheets("SUMMARY SHEET").Range("C6000").End(xlUp).Offset(1, 0).Value = Sheets("DO NOT DELETE").Range("A8").Value
lngPassed = 7
Application.ScreenUpdating = True
On Error GoTo 0
Exit Sub
Summarize_mod_Error:
Debug.Print "Error occurred at " & Now
Debug.Print "lngPassed shows a value of: " & lngPassed
Debug.Print Err.Number
Debug.Print Err.Description
End Sub[