Working Macro to Move onto Tab

[Roj47]

Many, many thanks to VoG for the following Macro :

Sub Copy_Formula()
'
' Copy_Formula Macro
' Macro recorded 04/05/2011 by Littlefair
'
'
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row - 1
Range("R11:AQ11").Copy
Range("R12:AQ" & LR).PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
End Sub

However, I am trying to have it run when a tab is selected. I have amended to the following to copy the formula on the sheet as shown, but it does not run to the bottom as the Picture allocated one does.

Where have I gone wrong please and how do I get it to work?

Thanking in advance for any assistance

Sub Copy_Formula()
'
' Copy_Formula Macro
' Macro recorded 04/05/2011 by Littlefair
'
'
Private Sub Worksheet_Activate()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row - 1
Sheets("VTC Detailed").Range("R11:AQ11").Copy
Application.EnableEvents = False
Sheets("VTC Detailed").Range("R12:AQ188").PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("VTC M&E Summary").Activate
Application.EnableEvents = True
End Sub

 

[Roj47]

I noted a mistake in the code (highlighted amendment in red), but unfortunately this is still failing miserably.

Do you have any suggestions for me to pick up the formula to run to the bottom of the list when I select a tab.

Many thanks.

Sub Copy_Formula()
'
' Copy_Formula Macro
' Macro recorded 04/05/2011 by Littlefair
'
'
Private Sub Worksheet_Activate()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row - 1
Sheets("VTC Detailed").Range("R11:AQ11").Copy
Application.EnableEvents = False
Sheets("VTC Detailed").Range("R12:AQ" & LR).PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("VTC M&E Summary").Activate
Application.EnableEvents = True
End Sub

 

[Roj47]

Closed out:

Sorry guys, really not wanting to spam or bump this at all.

Figured the problem through trial and error from a working VoG solution :

Sub Copy_Formula()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
'<o></o>
' Copy_Formula Macro<o></o>
' Macro recorded 04/05/2011 by Littlefair<o></o>
'<o></o>
'<o></o>
Private Sub Worksheet_Activate()<o></o>
Dim LR As Long<o></o>
LR = Sheets("VTC Detailed").Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row - 1<o></o>
Sheets("VTC Detailed").Range("R11:AQ11").Copy<o></o>
Application.EnableEvents = False<o></o>
Sheets("VTC Detailed").Range("R12:AQ" & LR).PasteSpecial Paste:=xlFormulas<o></o>
Sheets("VTC M&E Summary").Activate<o></o>
Application.EnableEvents = True<o></o>
Application.CutCopyMode = False<o></o>
End Sub