MAcro to Copy Worksheet and Worksheet Events

**max57** · 07-26-2009, 01:35 PM

Hi,
I have a worksheet that utilizes a Worksheet Change Event. I created a macro to copy the sheet and that works fine except for the fact that it doesn't contain the Worksheet Change event. Any idea on how I can do this (if possible) in a macro?

Thanks in advance.

Max57

**venkat1926** · 07-26-2009, 10:25 PM

not clear.
suggest you call the macro from within the worksheet change event
worksheet change event occurs when target value changes.

**wotadude** · 07-27-2009, 08:04 PM

i assume you might mean -

Code:

Sub CopyToNewWB()
' copies Sheet1 to a new workbook - sheet code included
    Activeworkbook.Sheets("Sheet1").Copy after:=Sheets(Sheets.Count)
End Sub

Sub CopyAsNewSht()
' creates copy of Sheet1 in sameworkbook - sheet code included
    Activeworkbook.Sheets("Sheet1").Copy
End Sub

**max57** · 07-27-2009, 10:40 PM

Hi all,
I had originally used a macro recorder macro and that's where my problem originated from. After reading the posts, I realized that I wasn't copying the Worksheet rather, only the worksheet cells. As a result, I wasn't getting the Worksheet Change event code copied into the new sheet.

I found a very good snippet of code that added a little more functionality to what I wanted to do and adopted it to my needs.

Code:

Option Explicit

Dim NewPageName As String

Sub NewPage()
Dim Sheets As Worksheet
Set Sheets = Sheet4
Sheets.Visible = True
Sheets.Copy After:=Worksheets(Worksheets.Count)
NewPageName = InputBox("What would you like to call your new Worksheet")
ActiveWindow.ActiveSheet.Name = NewPageName
Sheets.Visible = False

End Sub

Thank you for your suggestions which inevitably led me to this conclusion.

Max57