AW: Arbeiten mit versteckten Arbeitsblättern
20.10.2005 17:05:15
Ralf
Verzichte ich leider nicht... ;-)
Ehe ich jetzt Teil für Teil des Codes reinstelle, gleich alles:
Private Sub CheckBox7_Click() 'Revenue
If CheckBox7.Value = True Then
Sheets("Data Input").Select
Range("c10:bj10").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A2").Select
ActiveCell(1, 1).Value = ("Revenue")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox8_Click() 'Space Segment
If CheckBox8.Value = True Then
Sheets("Data Input").Select
Range("c13:bj13").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A3").Select
ActiveCell(1, 1).Value = ("Space segment")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox25_Click() 'Licenses
If CheckBox25.Value = True Then
Sheets("Data Input").Select
Range("c17:bj17").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A4").Select
ActiveCell(1, 1).Value = ("Licenses")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox24_Click() 'Field service and maintenance
If CheckBox24.Value = True Then
Sheets("Data Input").Select
Range("c21:bj21").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A5").Select
ActiveCell(1, 1).Value = ("Field service")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox23_Click() 'Installation
If CheckBox23.Value = True Then
Sheets("Data Input").Select
Range("c25:bj25").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A6").Select
ActiveCell(1, 1).Value = ("Installation")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox22_Click() 'Spare parts
If CheckBox22.Value = True Then
Sheets("Data Input").Select
Range("c29:bj29").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A7").Select
ActiveCell(1, 1).Value = ("Spare parts")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox21_Click() 'Materials to be sold
If CheckBox21.Value = True Then
Sheets("Data Input").Select
Range("c35:bj35").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A8").Select
ActiveCell(1, 1).Value = ("Materials to be sold")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox20_Click() 'Other cost of materials
If CheckBox20.Value = True Then
Sheets("Data Input").Select
Range("c39:bj39").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A9").Select
ActiveCell(1, 1).Value = ("Other cost of materials")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox19_Click() 'Personell expenses
If CheckBox19.Value = True Then
Sheets("Data Input").Select
Range("c43:bj43").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A10").Select
ActiveCell(1, 1).Value = ("Personell expenses")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox16_Click() 'Sales commission
If CheckBox16.Value = True Then
Sheets("Data Input").Select
Range("c48:bj48").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A11").Select
ActiveCell(1, 1).Value = ("Sales commission ")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox17_Click() 'Depreciation
If CheckBox17.Value = True Then
Sheets("Data Input").Select
Range("c52:bj52").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A12").Select
ActiveCell(1, 1).Value = ("Depreciation")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox18_Click() 'Other direct cost
If CheckBox18.Value = True Then
Sheets("Data Input").Select
Range("c56:bj56").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A13").Select
ActiveCell(1, 1).Value = ("Other direct cost")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox26_Click() 'Operating Expenses
If CheckBox26.Value = True Then
Sheets("Data Input").Select
Range("c60:bj60").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A14").Select
ActiveCell(1, 1).Value = ("Total operating expenses")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CheckBox27_Click() 'Cashflow
If CheckBox27.Value = True Then
Sheets("Data Input").Select
Range("c87:bj87").Select
Selection.Copy
Sheets("Data Storage").Select
Range("B15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A15").Select
ActiveCell(1, 1).Value = ("Cashflow")
Else
Sheets("Data Storage").Select
End If
End Sub
Private Sub CommandButton1_Click() 'OK
Application.ScreenUpdating = False
Worksheets("Data Storage").Visible = xlSheetVisible
Sheets("Data Storage").Select
For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If Cells(i, 3).Value = "" Then
Rows(i).Delete Shift:=xlUp
End If
Next
CheckBox7.Value = False 'Rücksetzen aller Checkboxes auf "Null"
CheckBox8.Value = False
CheckBox25.Value = False
CheckBox24.Value = False
CheckBox23.Value = False
CheckBox22.Value = False
CheckBox21.Value = False
CheckBox20.Value = False
CheckBox19.Value = False
CheckBox16.Value = False
CheckBox17.Value = False
CheckBox18.Value = False
CheckBox26.Value = False
UserForm2.Hide
Dim x As Integer
Dim y As Integer
Dim bereich As Range
x = (Sheets("Data Input").Range("B5").Value + 1) 'Monate aus "Data Input"!B5 übernehmen
y = ActiveSheet.UsedRange.Rows.Count 'Zeilen zählen
'Set bereich = Range("A1:S" & CStr(y)) '<< alter Code >>
Set bereich = Range(Cells(1, 1), Cells(y, x)) 'Quell Bereich für Diagramm
Charts.Add 'Diagramm erstellen
With ActiveChart
.ChartType = xlColumnClustered 'xlColumnStacked
.SetSourceData Source:=bereich, PlotBy:=xlRows
Application.CutCopyMode = False
.HasTitle = True
End With
Worksheets("Data Storage").Visible = xlSheetHidden
Application.ScreenUpdating = True
End Sub
Private Sub CommandButton2_Click() 'Cancel
CheckBox7.Value = False 'rücksetzen aller Checkboxes auf "Null"
CheckBox8.Value = False
CheckBox25.Value = False
CheckBox24.Value = False
CheckBox23.Value = False
CheckBox22.Value = False
CheckBox21.Value = False
CheckBox20.Value = False
CheckBox19.Value = False
CheckBox16.Value = False
CheckBox17.Value = False
CheckBox18.Value = False
CheckBox26.Value = False
UserForm2.Hide
Application.ScreenUpdating = False
Dim i As Long
Sheets("Data Storage").Select
For i = 20 To 2 Step -1 'Löscht alles
Rows(i).Delete Shift:=xlUp
Next
Application.ScreenUpdating = True
Worksheets("Data Storage").Visible = xlSheetHidden
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Label8_Click()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
'Application.ScreenUpdating = False
'Worksheets("Tabelle13").Visible = xlSheetVisible
Sheets("Data Storage").Select
For i = 20 To 2 Step -1 'Löscht alles
Rows(i).Delete Shift:=xlUp
Next
'Application.ScreenUpdating = True
End Sub