AW: Arbeitsblätter nummerieren und Zellenbezug
26.11.2007 09:50:00
Rainer
Hallo Sepp,
ich benötigte eine Vorlage für das Tabellenblatt und hab´s dann so gelöst.
Vielen Dank für die Hilfe,
Rainer
Sub NummernBlaetter()
Dim intStart As Integer, intEnde As Integer, intI As Integer
Dim objSh As Worksheet
On Error GoTo ErrExit
Application.ScreenUpdating = False
intStart = Application.InputBox("Startzahl:", "Tabellenblätter Anlegen", 1, 1)
If intStart = 0 Then Exit Sub
intEnde = Application.InputBox("Endzahl:", "Tabellenblätter Anlegen", intStart + 1, 1)
If intEnde = 0 Or intEnde
Sheets.Add Type:= _
"C: \pfad......... \Vorlage_Basar.xlt"
With ThisWorkbook
For intI = intStart To intEnde
If Not SheetExist(CStr(intI)) Then
Set objSh = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
objSh.Name = CStr(intI)
Sheets("Vorlage_Basar").Select
Cells.Select
Selection.Copy
objSh.Select
ActiveSheet.Paste
objSh.Range("B2") = intI
End If
Next
End With
ErrExit:
Application.ScreenUpdating = True
Set objSh = Nothing
End Sub
Private Function SheetExist(ByVal sheetName As String, Optional WbName As String) As Boolean
Dim wks As Worksheet
On Error GoTo ERRORHANDLER
If WbName = "" Then WbName = ThisWorkbook.Name
For Each wks In Workbooks(WbName).Worksheets
If wks.Name = sheetName Then SheetExist = True: Exit Function
Next
ERRORHANDLER:
SheetExist = False
End Function
Sub Blaetter()
For i = 100 To 130
Sheets.Add
ActiveSheet.Name = Str(i)
Cells(2, 2) = i
Next i
End Sub