Run Code on Inserted Worksheet

[billandrew]

I have code which needs to be executed when a user copies a worksheet into the workbook where the code exists.

I can include a shape and apply the code/macro to, however how would the reference to the sheet be. The sheets copied into the workbook all have different names.

 

[Jaafar Tribak]

Have you tried the Workbook_NewSheet event ?

 

[billandrew]

No I have not. I'll try that option.

Actually I copied the code to the personal workbook.

Thank you for the response.

 

[MARK858]

In general unless you have specified the workbook then if you have it in the personal workbook it will refer to the personal workbook.

Please post your full code.

 

[billandrew]

This is the code

Sub MergeColumns()


Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range



Application.ScreenUpdating = False


lr = Cells(Rows.Count, 6).End(xlUp).Row


For i = 9 To lr

Set rng1 = Range(Cells(i, 6), Cells(i + 1, 7))
With rng1
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True
End With


Set rng2 = Range(Cells(i, 9), Cells(i + 1, 10))
With rng2
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True
End With

Set rng3 = Range(Cells(i, 11), Cells(i + 1, 13))
With rng3
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True
End With


Set rng4 = Range(Cells(i, 4), Cells(i + 1, 5))
With rng4
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
.WrapText = True
End With

Next

Cells(1, 1).Select

End Sub