Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Delete sheet from a variable name list

2 views
Skip to first unread message

Nils Morten

unread,
Jun 1, 2011, 4:02:17 AM6/1/11
to
Hey guys
I have this record list. Column A contains a numeric list from 1 to n
When I activate a certain cell Ax I want my macro to check if the
sheet with the name as value in this cell exists (If so delete the
sheet) and then copy my template-sheet an rename this new template-
copy with the active cell value.

I have borrowed this macro from a friend, but it will not work
properly:

Sub CreateSheet()
Application.ScreenUpdating = False
Set cell = ActiveCell


If SheetExists(cell.Value) Then Sheets(cell.Value).Delete
Sheets("Template").Visible = True
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


Dim shTemplate As Worksheet, cell As Range

Application.DisplayAlerts = False


Set shTemplate = Sheets("Template")
shTemplate.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = cell.Value
Range("E5").Select
Set tbl = ActiveCell.CurrentRegion

Cells.Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("E5").Select
Sheets("MAL").Visible = False
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Function SheetExists(SheetName As String) As Boolean
Dim sh As Worksheet
On Error Resume Next
Set sh = Worksheets(sh)
If Not sh Is Nothing Then SheetExists = True
End Function

Will anyone guide me on this one?

Best Regards Snoopy

0 new messages