Running Macros in added sheets within same workbook

[Harry69]

Hi

I have a spreadsheet with various formulae across the columns. I have a macro which inserts a new row with format & formulae below a double-click.

Each worksheet represents a financial year & when I set it up, I covered 3 years. I now need to add a year and I have copied the data into a new sheet but the macro isn't working. I can't see any way to trigger it for the new sheet as all the sheets are listed on the VBA Project along with the Module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Did you copy the code into the sheet module for the new sheet you have created?

 

[Harry69]

Hi & welcome to MrExcel.
Did you copy the code into the sheet module for the new sheet you have created?

 

[Harry69]

Thanks for reply.

I right clicked on the sheet within VBA and Insert Module and pasted the module there before saving, closing & opening, but no action. Is that what you mean?

 

[Fluff]

That is the wrong place in needs to go in the sheet module, not a standard module.
In the project explorer double click the sheet it needs to work on & paste the code into the window that opens up.

 

[Harry69]

Perfect - thanks for your help.

 

[Fluff]

You're welcome & thanks for the feedback.