Duplicate and rename based on cell range

[Oldat43]

Hi all.

I have been tasked with updating the excel files used in my job, a lot of the staff are very very basic with anything computer related so I am trying to make the files more user friendly.
I work in a charity that depends on funding so asyou can imagin its hard to get skilled staff. I have an ok understanding of excel and how macros work but I am very new to VBA and was wonder would anyone help me figure my way through a couple of issues.

I have 2 wrok sheets one called Main and one called Template.
I need to create a VBA button on Main that will duplicate Template and name it from the value in cell A1 on the Main worksheet.

Heres the code I have so far.
Sub testcopy()
Dim wks As Worksheet
Set wks = ActiveSheet
ActiveSheet.Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = wks.Range("A1").Value
wks.Activate
End Sub

When I run it, it dups the page that is active and names it with the value in cell A1 of the active sheet.
How do I make it copy the Template sheet and rename from A1 on the Main sheet?

Thenks very much for the help.

 

[steve the fish]

Hello. Try:

Dim wsTem As Worksheet, wsMain as Worksheet
Set wsTem = Sheets("Template")
Set wsMain = Sheets("Main")
wsTem.Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = wsMain.Range("A1").Value
wsMain.Activate

 

[Oldat43]

Hello. Try:

Dim wsTem As Worksheet, wsMain as Worksheet
Set wsTem = Sheets("Template")
Set wsMain = Sheets("Main")
wsTem.Copy after:=Worksheets(Sheets.Count)
ActiveSheet.Name = wsMain.Range("A1").Value
wsMain.Activate

That works perfect...

Heres another problem, is there a way to stop it working or trow up an error if the sheet name already exsists?

 

[steve the fish]

I like to use a function:

Function SheetExists(shtName As String) As Boolean

Dim sht As Worksheet

On Error Resume Next
Set sht = Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing

End Function

Sub testcopy()

Dim wks As Worksheet
Set wsTem = Sheets("Template")
Set wsMain = Sheets("Main")

If Not SheetExists(wsMain.Range("A1").Value) Then
    wsTem.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.Name = wsMain.Range("A1").Value
    wsMain.Activate
End If

End Sub

 

[Oldat43]

I like to use a function:

Function SheetExists(shtName As String) As Boolean

Dim sht As Worksheet

On Error Resume Next
Set sht = Sheets(shtName)
On Error GoTo 0
SheetExists = Not sht Is Nothing

End Function

Sub testcopy()

Dim wks As Worksheet
Set wsTem = Sheets("Template")
Set wsMain = Sheets("Main")

If Not SheetExists(wsMain.Range("A1").Value) Then
    wsTem.Copy after:=Worksheets(Sheets.Count)
    ActiveSheet.Name = wsMain.Range("A1").Value
    wsMain.Activate
End If

End Sub

Thats fantastic. Thank you very much.

Can I pick your brain one more time.
Can you add that it switches to the sheet that was just created?
At the moment when I click the create button it stays on the Main sheet.

Thank you so much for the help.

 

[steve the fish]

Sure just delete wsMain.Activate

 

[Oldat43]

Sure just delete wsMain.Activate

Thank you very much. You have given me enough to now create what I hope will make things easier.