Macro that references to a changing cell

[bigsistar12]

I am creating a form that will have a filter. the main column reference will always be A but the row will change depending on the filter and data needed.

How can I write a macro to identify active cell (even if its filtered) and activate the entire row for that cell.

for example
A B C D
1 Michael
2 john
3 Sue
4 Frank
5 SUY
6 Jen

So If i need to filter for Jen then my active cell reference is A6 and I need to run my macro to select all info in Row 6 based on Active cell A6.

Background info: My macro will take information in a row and populate it in another sheet (form). So the active cell will always change.

 

[p45cal]

In the context of http://www.mrexcel.com/forum/showthread.php?t=538293.
Try this in the insurance1 sheet's code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Columns(1), Target) Is Nothing Then
  For Each cll In Worksheets("pricingcalculator").Range("B1,G1,B2,G2,B3,G3,B4,F4,H4,B5,F5,H5,B6,F6,H6,B8,F8,H8,B9,F9,H9,B10,F10,H10,B11,F11,H11,B12,F12,H12,B13,B14").Cells
    cll.Value = Target.Offset(, i).Value
    i = i + 1
  Next cll
End If
End Sub

Select a cell in column A of the insurance1 sheet, then switch to the pricingcalculator sheet. You don't need to activate/select anything at all in the code, so no need for the hyperlinks! Delete the line that adds the hyperlinks from the other macro in the other thread.

 

[bigsistar12]

ok last thing! BTW this works great! i need to learn this on my own thank you sooo much!

but if I protect sheet and then i run these commands it gives me errors is there anyway i can overwrite the protection within the macros?

 

[bigsistar12]

In the context of http://www.mrexcel.com/forum/showthread.php?t=538293.
Try this in the insurance1 sheet's code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Columns(1), Target) Is Nothing Then
  For Each cll In Worksheets("pricingcalculator").Range("B1,G1,B2,G2,B3,G3,B4,F4,H4,B5,F5,H5,B6,F6,H6,B8,F8,H8,B9,F9,H9,B10,F10,H10,B11,F11,H11,B12,F12,H12,B13,B14").Cells
    cll.Value = Target.Offset(, i).Value
    i = i + 1
  Next cll
End If
End Sub

Select a cell in column A of the insurance1 sheet, then switch to the pricingcalculator sheet. You don't need to activate/select anything at all in the code, so no need for the hyperlinks! Delete the line that adds the hyperlinks from the other macro in the other thread.

this didnt work once copied from calculator to insurance, then i go to insurance and click on cell a3 it does not repopulate calculator.
??

also Id like for this to be protected so that the macros are the only ones adding information and insterting rows into insurance.

 

[p45cal]

this didnt work once copied from calculator to insurance, then i go to insurance and click on cell a3 it does not repopulate calculator.
??

Where did you paste the code to?

 

[p45cal]

also Id like for this to be protected so that the macros are the only ones adding information and insterting rows into insurance.

I'm not going to write this project for you in its entirety.
Cherk out worksheet.protect with the UserInterfaceOnly argument set to true. Especially check the remarks section of the help file on this method regarding how long the sheet remains protected in this way.

 

[bigsistar12]

Where did you paste the code to?

Not sure what you mean? I copied it the way you posted it.

I apologize, I am not very macro saavy im learning as I go.

 

[p45cal]

In the insurance1 sheet's code module?
Right click the sheet's tab, choose View code, and paste the code, with no change to its name, where the flashing cursor is.