OTOTO
Board Regular
- Joined
- Dec 23, 2013
- Messages
- 209
Here's my problem: I am attempting to make cells in row Q double clickable and in doing so copy the data in that same row (to the left 4 cells to 15 cells to the left (of the double clickable cell)) into columns B through M in another sheet "RiskRegister" in the next empty row. I have found this code for locating the next empty cell in a column for the pasting code:
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & lMaxRows +1).select
I have the following code entered into the Worksheet's double click event:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address Like "$Q*" Then
currRow = Target.Cells(1, 1).row
Set currSheet = Target.Worksheet
End If
End Sub
...and I've recorded using relative references the following flawed Macro to run upon the double click:
Sub Macro17()
'
' Macro17 Macro
'
'
ActiveCell.Offset(0, -15).Range("A1:L1").Select
ActiveCell.Offset(0, -4).Range("A1").Activate
Selection.Copy
Sheets("Risk Register").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
The debugger is showing that the following line has an error as the identifier under the cursor is not recognized:
ActiveCell.Offset(0, -4).Range("A1").Activate
I am new to working with Excel in this manner and have no coding experience. Any help on how to get this double click event to do what I want would be greatly appreciated.
lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("B" & lMaxRows +1).select
I have the following code entered into the Worksheet's double click event:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address Like "$Q*" Then
currRow = Target.Cells(1, 1).row
Set currSheet = Target.Worksheet
End If
End Sub
...and I've recorded using relative references the following flawed Macro to run upon the double click:
Sub Macro17()
'
' Macro17 Macro
'
'
ActiveCell.Offset(0, -15).Range("A1:L1").Select
ActiveCell.Offset(0, -4).Range("A1").Activate
Selection.Copy
Sheets("Risk Register").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
The debugger is showing that the following line has an error as the identifier under the cursor is not recognized:
ActiveCell.Offset(0, -4).Range("A1").Activate
I am new to working with Excel in this manner and have no coding experience. Any help on how to get this double click event to do what I want would be greatly appreciated.