Hello - this is really a two part question. I'm having trouble getting a macro to work exactly the way I want it to and secondly, need (or I think I have to) combine with another macro on the same sheet that previously worked fine. I just don't know how to combine them.
Here's the first macro that works as I want it to:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim msg As String
'check if the row is greater than 1.
If Target.Row > 1 Then
'if the selected column date is less than the B1 date
If Range("C1") <> Cells(2, Target.Column) Then
'throw a messagebox
msg = MsgBox("This column is for a scorecard reporting period that is not open." & vbNewLine & " " & vbNewLine & "Please only enter data for the current reporting period, or contact the Investment Management Team for assistance." & vbNewLine & _
vbNewLine & "" & vbNewLine & "OK to move right to allowed date." & vbNewLine & "Cancel to view current reporting date." vbOKCancel)
'stop checking for events
Application.EnableEvents = False
Select Case msg
Case 1 'OK button pressed
'loop until the B1 date is less or equal to the header date
Do Until Range("C1") <= Cells(2, ActiveCell.Column)
'shift one cell to the right
ActiveCell.Offset(0, 1).Select
Loop
Case 2 'Cancel Button pressed
'select B1 so the user can change the date
Range("C1").Select
Case Else
End
End Select
'enable event checking
Application.EnableEvents = True
End If
End If
End Sub
The second macro, on the same sheet, current reads like this:
Sub Scorecard_2()
'
' Scorecard_2 Macro
'
Dim lookupVal As String
Dim lookupSheet As String
Dim lookupCell As String
Dim myCell As Range
'
Sheets("Scorecard Data (2)").Select
'Find the current period/date
Range("C1").Select
'the line below gets an error:Run Time Error 424, Object Required
lookupVal = Target.Value
'Find the column that corresponds to the date in C1
Rows("2:2").Find(What:=lookupVal, After:=Range("B3"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
lookupCell = ActiveCell.Offset(37, 0)
' Set range to go to
Set myCell = Range(lookupCell)
myCell.Select
'Will always be rows 39 to 49 but column will vary based on what's above
Range("E39:E49").Select
Application.CutCopyMode = False
Selection.Copy
'Should always be pasted one column to the right
Range("F39").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C1").Select
Application.CutCopyMode = False
Sheets("Administration").Select
Range("C2").Select
End Sub
I thought it would be easier to get the two macros working independently and then combine them. As I've indicated, the macro above gets an error and then doesn't work quite the way I want it to on a couple other lines where I'm not sure how to make relative references. Any thoughts?
For reference, Cell C1 contains a drop down date selection (e.g. Mar-17) and the lookup dates are on row 2 from column C-N in the same format.
Thanks in advance.
Here's the first macro that works as I want it to:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim msg As String
'check if the row is greater than 1.
If Target.Row > 1 Then
'if the selected column date is less than the B1 date
If Range("C1") <> Cells(2, Target.Column) Then
'throw a messagebox
msg = MsgBox("This column is for a scorecard reporting period that is not open." & vbNewLine & " " & vbNewLine & "Please only enter data for the current reporting period, or contact the Investment Management Team for assistance." & vbNewLine & _
vbNewLine & "" & vbNewLine & "OK to move right to allowed date." & vbNewLine & "Cancel to view current reporting date." vbOKCancel)
'stop checking for events
Application.EnableEvents = False
Select Case msg
Case 1 'OK button pressed
'loop until the B1 date is less or equal to the header date
Do Until Range("C1") <= Cells(2, ActiveCell.Column)
'shift one cell to the right
ActiveCell.Offset(0, 1).Select
Loop
Case 2 'Cancel Button pressed
'select B1 so the user can change the date
Range("C1").Select
Case Else
End
End Select
'enable event checking
Application.EnableEvents = True
End If
End If
End Sub
The second macro, on the same sheet, current reads like this:
Sub Scorecard_2()
'
' Scorecard_2 Macro
'
Dim lookupVal As String
Dim lookupSheet As String
Dim lookupCell As String
Dim myCell As Range
'
Sheets("Scorecard Data (2)").Select
'Find the current period/date
Range("C1").Select
'the line below gets an error:Run Time Error 424, Object Required
lookupVal = Target.Value
'Find the column that corresponds to the date in C1
Rows("2:2").Find(What:=lookupVal, After:=Range("B3"), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
lookupCell = ActiveCell.Offset(37, 0)
' Set range to go to
Set myCell = Range(lookupCell)
myCell.Select
'Will always be rows 39 to 49 but column will vary based on what's above
Range("E39:E49").Select
Application.CutCopyMode = False
Selection.Copy
'Should always be pasted one column to the right
Range("F39").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C1").Select
Application.CutCopyMode = False
Sheets("Administration").Select
Range("C2").Select
End Sub
I thought it would be easier to get the two macros working independently and then combine them. As I've indicated, the macro above gets an error and then doesn't work quite the way I want it to on a couple other lines where I'm not sure how to make relative references. Any thoughts?
For reference, Cell C1 contains a drop down date selection (e.g. Mar-17) and the lookup dates are on row 2 from column C-N in the same format.
Thanks in advance.
