Kopiering af sheet, med definering af navn

Løst ved:

' Purpose:
' Parameter: sourceWS        - Worksheet to copy
'            destinationName - Name of the duplicate WS
Function CopyWS(sourceWS As Worksheet, destinationName As String) As Worksheet

  ' If a sheet name "destinationName" already exists -> delete sheet
  If WorksheetExists(destinationName) Then
    ThisWorkbook.Sheets(destinationName).Delete
  End If
 
  sourceWS.Activate
  sourceWS.Copy after:=sourceWS
  Set CopyWS = ThisWorkbook.Worksheets(ActiveSheet.Index)
  CopyWS.Name = destinationName
End Function

Nedenstående er fra: http://www.mrexcel.com/forum/showthread.php?t=3228 (Spencer Jobe)
Public Function WorksheetExists(ByVal WorksheetName As String) As Boolean
On Error Resume Next
  WorksheetExists = (Sheets(WorksheetName).Name <> "")
On Error GoTo 0
End Function


Pt. er eneste minus at den spørger om "ark" skal slettes!

Nedenfor CopyWS uden "confirmation" af delete.
Det slås fra ved:
Application.DisplayAlerts = False
Og slås til ved:
Application.DisplayAlerts = True


Function CopyWS(sourceWS As Worksheet, destinationName As String) As Worksheet

  ' If a sheet name "destinationName" already exists -> delete sheet
  If WorksheetExists(destinationName) Then
    Application.DisplayAlerts = False
      ThisWorkbook.Sheets(destinationName).Delete
    Application.DisplayAlerts = True
  End If
 
  sourceWS.Activate
  sourceWS.Copy after:=sourceWS
  Set CopyWS = ThisWorkbook.Worksheets(ActiveSheet.Index)
  CopyWS.Name = destinationName
End Function