Copy code when Copying worksheet

[twl2009]

I have used this bit of code to copy a worksheet, unfortunately there is a small bit of code that is in the original sheet, but doesn't copy across.

Is there some paste special I can use to copy the code across.

Thanks

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}span.s1 {color: #011993}</style>Set MyActiveCell = ActiveCell
        Sheets("Timesheet").Cells.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
        ActiveSheet.Name = Target.Value
  
        ActiveWindow.DisplayGridlines = False
        ActiveWindow.DisplayHeadings = False
        ActiveWindow.SplitColumn = 0
        ActiveWindow.SplitRow = 3
        ActiveWindow.FreezePanes = True
        ActiveSheet.Range("A1:AA200").Locked = True
        ActiveSheet.Range("D12:M18").Locked = False
        ActiveSheet.Range("M7:P7").Locked = False
        ActiveSheet.Range("Q12:Q18").Locked = False


        ActiveSheet.Range("D12").Select
        ActiveSheet.Protect


        Sheets("Labour Week").Activate
[COLOR=#000000][FONT=Menlo]        Application.Goto MyActiveCell
[/FONT][/COLOR]

The code that needs to be copied is

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>Private Sub Worksheet_Change(ByVal Target As Range)


  
    If Target.Count > 1 Then Exit Sub
    
    If Target.Address(False, False) = "K2" Then Call SaveSheet


[COLOR=#011993][FONT=Menlo]End[/FONT][/COLOR][FONT=Menlo] [/FONT][COLOR=#011993][FONT=Menlo]Sub
[/FONT][/COLOR]

 

[gallen]

You are just copying the cells. You need to copy the actual sheet object

Change this part

[COLOR=#333333]        Sheets("Timesheet").Cells.Copy
[/COLOR]        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Paste
 [COLOR=#333333]       Application.CutCopyMode = False[/COLOR]

To

[COLOR=#333333]Sheets("Timesheet").Copy After:=Sheets(Sheets.Count)[/COLOR]

 

[BarryL]

I think you can also use code to copy code across. like:

Dim src As CodeModule, dest As CodeModule

Set src = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
Set dest = Workbooks("Book1").VBProject.VBComponents("ThisWorkbook") _
    .CodeModule

dest.DeleteLines 1, dest.CountOfLinesdest.AddFromString src.Lines(1, src.CountOfLines)

[code]

but obviously gallen's method is the best way

 

[twl2009]

Hi Gallen,

That sort of works, but because it doesnt open the copied sheet the rest of the code starts working on the sheet I have open.
I have tried adding

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}</style>        Sheets("Timesheet").Copy After:=Sheets(Sheets.Count)
[COLOR=#000000][FONT=Menlo]        Sheets("Timesheet(2)").Activate
[/FONT][/COLOR]

But I get a subscript out of range error

 

[gallen]

Try

Sheets(Sheets.count).Activate

 

[twl2009]

Haha that works, but activates the original not the copy so the next time I enter a name there isnt an original to copy only Timesheet(2) so I get an error

 

[gallen]

I tested it, and it activated the New sheet?

so

        Sheets("Timesheet").Copy After:=Sheets(Sheets.Count)
        [COLOR=#574123]Sheets(Sheets.count).Activate[/COLOR]

Should create a new sheet then activate it

I must add though that I hate using activate ANYWHERE. I try to use fully qualified references and/or variables to ensure my code is being run on the sheet I intend it to.

 

[twl2009]

That is the code that ive got at the moment. It opens the original not the copy.
Is there a way I could call sheet by name, it is listed as Timesheet(2), but I tried that and it gave error

 

[gallen]

I'm really confused so if you used this code, you are saying that the messagebox says "Timesheet is activated" and not "Timesheet(1) is activated"

        Sheets("Timesheet").Copy After:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Activate
        MsgBox ActiveSheet.Name & " is activated"

 

[BarryL]

shouldn't you have a space after timesheet like below?

Sheets("Timesheet (2)").Activate