In column F, I have dates. I would like to program a macro that would do the following:
For the data starting from Row 7, if the date in Column F is greater than the previous month (means greater than 1st July 2015), copy that entire row and paste special values starting from row 46.
If the date in Column F is before 1st July 2015, output the data as the earliest and last date (11/05/2015 - 16/05/2015) and the sums from column H (60), and paste it on Row 45.
When the run the code, there is a "Type Mismatch Error" on the coding line Rows("InputRow:InputRow").Select .
Any help will be appreciated, thanks.
For the data starting from Row 7, if the date in Column F is greater than the previous month (means greater than 1st July 2015), copy that entire row and paste special values starting from row 46.
If the date in Column F is before 1st July 2015, output the data as the earliest and last date (11/05/2015 - 16/05/2015) and the sums from column H (60), and paste it on Row 45.
When the run the code, there is a "Type Mismatch Error" on the coding line Rows("InputRow:InputRow").Select .
Any help will be appreciated, thanks.
Code:
Sub Summary()
Dim LastDataRow As Long
Dim DataRow As Long
Dim cDates As Long
Dim cAmounts As Long
Dim cI As Double
Dim cAK As Double
Dim cBM As Double
Dim CutoffDate As Date
Dim EarliestOldDate As Date
Dim LatestOldDate As Date
Dim SumOfOld As Long
Dim OldMonthsRow As Long
Dim InputRow As Long
Dim OutputRow As Long
Dim TheDate As Date
Dim TheAmount As Long
Dim ws As Worksheet
' INITIALIZE
' Assume we're operating on the activesheet
Set ws = ActiveSheet
' Assume data is a contiguous block
LastDataRow = ws.Range("H" & CStr(7)).End(xlDown).Row
' Calculate row where sum of old months goes
OldMonthsRow = 45
' Calculate the cutoff date = first date of current month
CutoffDate = DateSerial(Year(Date), Month(Date) - 1, 1)
' Column where dates are
cDates = 6
' Column where amounts are
cAmounts = 8
' Initialize earliest and latest old dates, and sum of old
EarliestOldDate = DateSerial(3000, 12, 31) ' Way out in the future
LatestOldDate = DateSerial(1904, 1, 1) ' Way back in the past
SumOfOld = 0
' PROCESS THE DATA
OutputRow = OldMonthsRow
For InputRow = 7 To LastDataRow
TheDate = ws.Cells(InputRow, 6)
TheAmount = ws.Cells(InputRow, 8)
If TheDate >= CutoffDate Then
' Add at the bottom of the summary table
OutputRow = OutputRow + 1
Rows("InputRow:InputRow").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=15
Rows("OutputRow:OutputRow").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else
' Update results for previous months
EarliestOldDate = IIf(TheDate < EarliestOldDate, TheDate, EarliestOldDate)
LatestOldDate = IIf(TheDate > LatestOldDate, TheDate, LatestOldDate)
SumOfOld = SumOfOld + TheAmount
End If
Next InputRow
' WRITE RESULTS TO SUMMARY ROW
ws.Cells(OldMonthsRow, cDates).Formula = Format(EarliestOldDate, "dd/mm/yyyy") & " - " & Format(LatestOldDate, "dd/mm/yyyy")
ws.Cells(OldMonthsRow, cAmounts).Formula = SumOfOld
Set ws = Nothing
End Sub