Copy Activecell value to Column Header at Intersect

jaybels · Jun 22nd 2016

Hi there,

Was wondering if anyone knows how to do the following:

I have a range of week commencing dates (from cells Q1:AM1).
In column L, I have corresponding w/c dates. What I need to do is run an event macro that will trigger when a date is typed into the activecell in column L (for example), it will then find the corresponding w/c date in range (Q1:AM1), i.e, the Column Headers. However, the value will be put into the intersect of the current activecell and the w/c column header date. The value needs to be copied from column E. I hope this makes sense.

I have attached the spreadsheet that I have entered some code into. The macro runs by pressing CNTRL & t.

I can only get it to highlight the w/c column at the moment.

If anyone can help, it would be greatly appreciated.

Thanks in advance.

Jaybels.

jaybels · Jun 22nd 2016

Re: Copy Activecell value to Column Header at Intersect

I have managed to solve this problem myself after much painstaking research.

I have posted the solution for the benefit of the forum users:

Sub Select_Today()
mr = ActiveCell.row
Set rng = ActiveCell
On Error Resume Next
Range("Q1:AM1").Find(rng, , xlValues, xlWhole).Activate
mc = Chr(ActiveCell.Column + 64)

Range(mc & mr).Select

End Sub

Jaybels

skywriter · Jun 23rd 2016

Re: Copy Activecell value to Column Header at Intersect

If you install this code into the sheet module and click on a date in column L that exists in row 1, then it will take you to the matching column, in the target row.

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column <> 12 Then Exit Sub
    Dim x
    On Error GoTo Err_Handler
    x = Application.Match(Target, Target.Parent.Rows("1:1"), 0)
    If Not IsError(x) Then
        Application.EnableEvents = False
        Application.Goto Target.Parent.Cells(Target.Row, x)
    Else
        Exit Sub
    End If
Err_Handler:
    Application.EnableEvents = True
End Sub

Display More

jaybels · Jun 23rd 2016

Re: Copy Activecell value to Column Header at Intersect

Thanks Skywriter.

It's much cleaner code than what I was going to use.

Jaybels

skywriter · Jun 23rd 2016

Re: Copy Activecell value to Column Header at Intersect

My pleasure, thanks for the feedback. :cool:

Copy Activecell value to Column Header at Intersect

Participate now!

Share