Copy The Code from one sheet to another

Question

I am trying to write a code that create another sheet and paste the code of the second sheet on it, the program also will delete the sheet if it already exists

Application.DisplayAlerts = False
Sheets("Calcs").Delete
Application.DisplayAlerts = True

With ThisWorkbook
        .Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = "Calcs"
End With

Dim CodeCopy As VBIDE.CodeModule
Dim CodePaste As VBIDE.CodeModule
Dim numLines As Integer

Set CodeCopy = ActiveWorkbook.VBProject.VBComponents("Sheet2").CodeModule
Set CodePaste = ActiveWorkbook.VBProject.VBComponents("Calcs").CodeModule

numLines = CodeCopy.CountOfLines

CodePaste.AddFromString CodeCopy.Lines(1, numLines)

Is not working and I dont know why

What error do you get? Have you run the code in debug? Have you checked [here](https://support.microsoft.com/en-us/kb/813969) ? — genespos, Jun 30 '16 at 12:38
Would it not be easier to create a Very Hidden worksheet containing the code you need and then just copy the whole sheet and rename it as needed? — Darren Bartrup-Cook, Jun 30 '16 at 12:41
The error is @genespos Run- Time Error 9 Subscript out of range, By using the debug the error is about the line when I set the target sheet (Sheet calcs) — Aus Mazin, Jun 30 '16 at 12:44
@DarrenBartrup-Cook I dont know how to do it, I am just started to learn how to use VBA — Aus Mazin, Jun 30 '16 at 12:45

score 0 · Answer 1 · edited May 23 '17 at 12:08

I think it's not working because of the name of your sheet. In the VBA Project window you can see that your sheets have two names: Sheet1 (Sheet1). So when you add your sheet and rename it, the name will be Sheet##(Calcs) but when you write ActiveWorkbook.VBProject.VBComponents("Calcs").CodeModule you need to use "Sheet##" which is the codename instead of "Calcs". It is better explained here: Excel tab sheet names vs. Visual Basic sheet names

What I suggest is to declare your sheet when you create it and write ...VBComponents(TheNameYouDeclared.CodeName).CodeModule

The code you gave us plus what I suggest gives you:

Application.DisplayAlerts = False
Sheets("Calcs").Delete
Application.DisplayAlerts = True

With ThisWorkbook
        .Sheets.Add(after:=.Sheets(.Sheets.Count)).Name = "Calcs"
End With

Dim MySheet As Worksheet
Set MySheet = ThisWorkbook.Sheets("Calcs")

Dim CodeCopy As String
Dim CodePaste As String
Dim numLines As Integer

CodeCopy = ActiveWorkbook.VBProject.VBComponents("Sheet1").CodeModule
CodePaste = ActiveWorkbook.VBProject.VBComponents(MySheet.CodeName).CodeModule

numLines = CodeCopy.CountOfLines

CodePaste.AddFromString CodeCopy.Lines(1, numLines)

Is it working for you?

score 0 · Answer 2 · answered Jun 30 '16 at 12:54

Create a template worksheet containing the code you need - then just copy this to create your new sheet.

In my code I have used the codename of the template sheet rather than the name that appears on the tab (which can be changed outside the VBE) - it's the name not in brackets in your Microsoft Excel Objects and can be updated with the (Name) property in the Properties tab.

Sub Test()

    If WorkSheetExists("Calcs") Then
        Application.DisplayAlerts = False
        ThisWorkbook.Worksheets("Calcs").Delete
        Application.DisplayAlerts = True
    End If

    With shtTemplate 'Use codename rather than actual name.
        .Visible = xlSheetVisible
        .Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        .Visible = xlSheetVeryHidden
    End With

    ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = "Calcs"

End Sub

Public Function WorkSheetExists(SheetName As String, Optional WrkBk As Workbook) As Boolean
    Dim wrkSht As Worksheet

    If WrkBk Is Nothing Then
        Set WrkBk = ThisWorkbook
    End If

    On Error Resume Next
        Set wrkSht = WrkBk.Worksheets(SheetName)
        WorkSheetExists = (Err.Number = 0)
        Set wrkSht = Nothing
    On Error GoTo 0
End Function

Copy The Code from one sheet to another

2 Answers2