Worksheet_Calculate
Range("B8:B37")) are formulas returning strings such as "Cash" or Equities", as two examples, then data is copied to a certain sheet for that string. There are about thirteen different sheets/strings.
This Select Case fails because Target is the entire range of B8:B37.
How do I zero in on the cell that just calculated to make my data transfer correctly?
I have to assume that any of the strings will appear more than once within the B8:B37 cell range. So if a calculate event occurs and returns "Cash" to a cell, there may be one or more strings "Cash" already in the range. I only want the cell that just recalculated.
And if more than one cell changes with a calculate event then it seems none of this will work.
Thanks.
Howard
Private Sub Worksheet_Calculate()
Dim Target As Range
Set Target = Range("B8:B37")
If Not Intersect(Target, Range("B4:B37")) Is Nothing Then
Select Case Target.Value
Case Is = "Cash"
'Target.Resize(1, 5).Copy Sheets("Aug 2016 Cash Journal").Range("A" & Rows.Count).End(xlUp)(2)
Target.Resize(1, 5).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)
Case Is = "Equity"
'Target.Resize(1, 5).Copy Sheets("Aug 2016 Equity Journal").Range("A" & Rows.Count).End(xlUp)(2)
Target.Resize(1, 5).Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2)
Case Else
'MsgBox "No Copy"
End Select
End If
End Sub
|