Copy and paste EVERYTHING from a template using vba?

[Dan91]

Hi
im having some problems copy / paste from one sheet to another.
I have is a 'home' sheet and a 'Base_sheet' sheet. What i wish to do is click a button on the 'home' sheet, and it will copy everything from the 'Base_Sheet' , create a new sheet and paste all the data on the new sheet. it also needs to paste the buttons, formulas. literally copy and paste everything. i have a popup box asking what to call the new sheet.

here is the code i currently have and its not working. any help would be greatly appreciated.

Private Sub NewStudent_Click()

  
   ' Declare variables
    Dim sheetName As String
    Dim newSheet As Worksheet
    Dim baseSheet As Object ' Use Object type to handle both worksheet and module sheets
    Dim baseCodeModule As Object ' Use Object type to handle both worksheet and module sheets
    Dim newCodeModule As Object ' Use Object type to handle both worksheet and module sheets
    Dim codeLines As Variant
    
    Set baseSheet = ThisWorkbook.Sheets("Base_Sheet")
    
    ' Check if the sheet is a worksheet or a module sheet
    If TypeName(baseSheet) = "Worksheet" Then
        ' The sheet is a worksheet
  [COLOR=rgb(250, 197, 28)]      Set baseCodeModule = baseSheet.CodeModule[/COLOR]
    Else
        ' The sheet is a module sheet
        Set baseCodeModule = baseSheet.VBProject.VBComponents(baseSheet.Name).CodeModule
    End If
    
    ' Show popup box to enter sheet name
    sheetName = InputBox("Enter the name of the new sheet:")
    
    ' Check if sheet name is not empty
    If sheetName <> "" Then
        ' Create new sheet
        Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        newSheet.Name = sheetName
        
        ' Copy data and objects from base sheet to new sheet
        baseSheet.Cells.Copy Destination:=newSheet.Cells
        
        ' Loop through all the shapes on the base sheet and copy them to the new sheet
        Dim i As Integer
        i = 1
        For Each sh In baseSheet.Shapes
            sh.Copy
            newSheet.Paste
            newSheet.Shapes(newSheet.Shapes.Count).Top = baseSheet.Shapes(i).Top
            newSheet.Shapes(newSheet.Shapes.Count).Left = baseSheet.Shapes(i).Left
            i = i + 1
        Next sh
        
        ' Place sheet name in cell A3
        newSheet.Range("A3").Value = sheetName
        
        ' Copy VBA code from base sheet to new sheet
        codeLines = baseCodeModule.Lines(1, baseCodeModule.CountOfLines)
        Set newCodeModule = ThisWorkbook.VBProject.VBComponents(newSheet.CodeName).CodeModule ' Use the CodeName property to get the module name of the new sheet
        newCodeModule.InsertLines 1, Join(codeLines, vbCrLf)
    End If
End Sub

There is a runtime error 438 here : Set baseCodeModule = baseSheet.CodeModule

Thanks

/Dan

 

[bebo021999]

Why not dupplicate the sheet (Copy) ?
For example:

Option Explicit
Private Sub NewStudent_Click()
Sheets("Base_Sheet").Copy after:=Sheets(Sheets.Count)
With ActiveSheet
    .Name = InputBox("Enter the name of the new sheet:")
    .Range("A3").Value = .Name
End With
End Sub

 

[Dan91]

I mean, yeah! wow that should work. ill give it a test but thank you! is so simple. haha