VBA beschreibt Zelle nicht
Jack
Heut ist definitiv nicht mein Tag.
Das Makro macht nämlich nicht das was es soll
Das Problem
Und zwar ab Y2 schreibt er weder in Ausgabe KFA12 Yn noch in Powerpoint KFA12 Yn die Werte in die dazugehörigen Zellen
also
Sheets(CStr("E(" & y1 & ")")).Cells(4, 4).Value = y2 (Ausgabe)
und
Sheets(CStr("E(" & y1 & ")")).Cells(14, 2).Value = y2 (Powerpoint)
Im ersten Block macht er das aber seltsamer weise nicht im 2. Block
Ich hab es schonmal im Einzelschritt durchlaufen lassen, jedoch macht er an der Stelle einfach nix, obwohl die werte Y1-n "gefüllt" sind.
Wer weiß Rat ?
Sub blattnamen()
Dim i As Integer, x, y1, y2, y3, y4, y5, y6, z As String
On Error Resume Next
Application.ScreenUpdating = False
With ActiveSheet
strTab = .Range("B6")
With Worksheets("How-To")
y1 = .Cells(18, 8).Value
y2 = .Cells(19, 8).Value
y3 = .Cells(20, 8).Value
y4 = .Cells(21, 8).Value
y5 = .Cells(22, 8).Value
y6 = .Cells(23, 8).Value
End With
With Worksheets("Auswertung(alle)")
If y1 = "" Then GoTo Ende
.Cells(34, 5).Value = y1
'muster nehmen und Umbennenen
Application.Calculation = xlCalculationManual
'EingabeKFA12
Sheets(CStr("E(" & y1 & ")")).Activate
If Err.Number = 0 Then GoTo next1
Sheets("EingabeKFA12").Select
Sheets("EingabeKFA12").Name = CStr("E(" & y1 & ")")
Sheets(CStr("E(" & y1 & ")")).Cells(1, 4).Value = y1
Sheets(CStr("E(" & y1 & ")")).Select
Sheets(CStr("E(" & y1 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "EingabeKFA12"
ActiveSheet.Tab.ColorIndex = 1
'AusgabeKFA12
Sheets("AusgabeKFA12").Select
Sheets("AusgabeKFA12").Name = CStr("A(" & y1 & ")")
Sheets(CStr("E(" & y1 & ")")).Cells(4, 4).Value = y1
Sheets(CStr("A(" & y1 & ")")).Select
Sheets(CStr("A(" & y1 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "AusgabeKFA12"
ActiveSheet.Tab.ColorIndex = 1
'Powerpoint
Sheets("Powerpoint").Select
Sheets("Powerpoint").Name = CStr("P(" & y1 & ")")
Sheets(CStr("E(" & y1 & ")")).Cells(14, 2).Value = y1
Sheets(CStr("P(" & y1 & ")")).Select
Sheets(CStr("P(" & y1 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Powerpoint"
ActiveSheet.Tab.ColorIndex = 1
next1:
If y2 = "" Then GoTo Ende
.Cells(34, 6).Value = y2
'EingabeKFA12 y2
Sheets(CStr("E(" & y1 & ")")).Activate
If Err.Number = 0 Then GoTo next2
Sheets("EingabeKFA12").Select
Sheets("EingabeKFA12").Name = CStr("E(" & y2 & ")")
Sheets(CStr("E(" & y2 & ")")).Cells(1, 4).Value = y2
Sheets(CStr("E(" & y2 & ")")).Select
Sheets(CStr("E(" & y2 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "EingabeKFA12"
ActiveSheet.Tab.ColorIndex = 20
'AusgabeKFA12 y2
Sheets("AusgabeKFA12").Select
Sheets("AusgabeKFA12").Name = CStr("A(" & y2 & ")")
Sheets(CStr("E(" & y2 & ")")).Cells(4, 4).Value = y2
Sheets(CStr("A(" & y2 & ")")).Select
Sheets(CStr("A(" & y2 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "AusgabeKFA12"
ActiveSheet.Tab.ColorIndex = 20
'Powerpoint y2
Sheets("Powerpoint").Select
Sheets("Powerpoint").Name = CStr("P(" & y2 & ")")
Sheets(CStr("E(" & y2 & ")")).Cells(14, 2).Value = y2
Sheets(CStr("P(" & y2 & ")")).Select
Sheets(CStr("P(" & y2 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Powerpoint"
ActiveSheet.Tab.ColorIndex = 20
next2:
If y3 = "" Then GoTo Ende
.Cells(34, 7).Value = y3
'EingabeKFA12 y3
Sheets(CStr("E(" & y3 & ")")).Activate
If Err.Number = 0 Then GoTo next3
Sheets("EingabeKFA12").Select
Sheets("EingabeKFA12").Name = CStr("E(" & y3 & ")")
Sheets(CStr("E(" & y3 & ")")).Cells(1, 4).Value = y3
Sheets(CStr("E(" & y3 & ")")).Select
Sheets(CStr("E(" & y3 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "EingabeKFA12"
ActiveSheet.Tab.ColorIndex = 30
'AusgabeKFA12 y3
Sheets("AusgabeKFA12").Select
Sheets("AusgabeKFA12").Name = CStr("A(" & y3 & ")")
Sheets(CStr("E(" & y3 & ")")).Cells(4, 4).Value = y3
Sheets(CStr("A(" & y3 & ")")).Select
Sheets(CStr("A(" & y3 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "AusgabeKFA12"
ActiveSheet.Tab.ColorIndex = 30
'Powerpoint y3
Sheets("Powerpoint").Select
Sheets("Powerpoint").Name = CStr("P(" & y3 & ")")
Sheets(CStr("E(" & y3 & ")")).Cells(14, 2).Value = y3
Sheets(CStr("P(" & y3 & ")")).Select
Sheets(CStr("P(" & y3 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Powerpoint"
ActiveSheet.Tab.ColorIndex = 30
next3:
If y4 = "" Then GoTo Ende
.Cells(34, 8).Value = y4
'EingabeKFA12 y4
Sheets(CStr("E(" & y4 & ")")).Activate
If Err.Number = 0 Then GoTo next4
Sheets("EingabeKFA12").Select
Sheets("EingabeKFA12").Name = CStr("E(" & y4 & ")")
Sheets(CStr("E(" & y4 & ")")).Cells(1, 4).Value = y4
Sheets(CStr("E(" & y4 & ")")).Select
Sheets(CStr("E(" & y4 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "EingabeKFA12"
ActiveSheet.Tab.ColorIndex = 40
'AusgabeKFA12 y4
Sheets("AusgabeKFA12").Select
Sheets("AusgabeKFA12").Name = CStr("A(" & y4 & ")")
Sheets(CStr("E(" & y4 & ")")).Cells(4, 4).Value = y4
Sheets(CStr("A(" & y4 & ")")).Select
Sheets(CStr("A(" & y4 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "AusgabeKFA12"
ActiveSheet.Tab.ColorIndex = 40
'Powerpoint y4
Sheets("Powerpoint").Select
Sheets("Powerpoint").Name = CStr("P(" & y4 & ")")
Sheets(CStr("E(" & y4 & ")")).Cells(14, 2).Value = y4
Sheets(CStr("P(" & y4 & ")")).Select
Sheets(CStr("P(" & y4 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Powerpoint"
ActiveSheet.Tab.ColorIndex = 40
next4:
If y5 = "" Then GoTo Ende
.Cells(34, 8).Value = y5
'EingabeKFA12 y5
Sheets(CStr("E(" & y5 & ")")).Activate
If Err.Number = 0 Then GoTo next5
Sheets("EingabeKFA12").Select
Sheets("EingabeKFA12").Name = CStr("E(" & y5 & ")")
Sheets(CStr("E(" & y5 & ")")).Cells(1, 4).Value = y5
Sheets(CStr("E(" & y5 & ")")).Select
Sheets(CStr("E(" & y5 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "EingabeKFA12"
ActiveSheet.Tab.ColorIndex = 50
'AusgabeKFA12 y5
Sheets("AusgabeKFA12").Select
Sheets("AusgabeKFA12").Name = CStr("A(" & y5 & ")")
Sheets(CStr("E(" & y5 & ")")).Cells(4, 4).Value = y5
Sheets(CStr("A(" & y5 & ")")).Select
Sheets(CStr("A(" & y5 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "AusgabeKFA12"
ActiveSheet.Tab.ColorIndex = 50
'Powerpoint y5
Sheets("Powerpoint").Select
Sheets("Powerpoint").Name = CStr("P(" & y5 & ")")
Sheets(CStr("E(" & y5 & ")")).Cells(14, 2).Value = y5
Sheets(CStr("P(" & y5 & ")")).Select
Sheets(CStr("P(" & y5 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Powerpoint"
ActiveSheet.Tab.ColorIndex = 50
next5:
If y5 = "" Then GoTo Ende
.Cells(34, 8).Value = y6
'EingabeKFA12 y6
Sheets(CStr("E(" & y6 & ")")).Activate
If Err.Number = 0 Then GoTo Ende
Sheets("EingabeKFA12").Select
Sheets("EingabeKFA12").Name = CStr("E(" & y6 & ")")
Sheets(CStr("E(" & y6 & ")")).Cells(1, 4).Value = y6
Sheets(CStr("E(" & y6 & ")")).Select
Sheets(CStr("E(" & y6 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "EingabeKFA12"
ActiveSheet.Tab.ColorIndex = 60
'AusgabeKFA12 y6
Sheets("AusgabeKFA12").Select
Sheets("AusgabeKFA12").Name = CStr("A(" & y6 & ")")
Sheets(CStr("E(" & y6 & ")")).Cells(4, 4).Value = y6
Sheets(CStr("A(" & y6 & ")")).Select
Sheets(CStr("A(" & y6 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "AusgabeKFA12"
ActiveSheet.Tab.ColorIndex = 60
'Powerpoint y5
Sheets("Powerpoint").Select
Sheets("Powerpoint").Name = CStr("P(" & y6 & ")")
Sheets(CStr("E(" & y6 & ")")).Cells(14, 2).Value = y6
Sheets(CStr("P(" & y6 & ")")).Select
Sheets(CStr("P(" & y6 & ")")).Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Powerpoint"
ActiveSheet.Tab.ColorIndex = 60
End With
End With
Ende:
Application.Calculation = xlCalculationAutomatic
'Berechnen in RFP Aktivieren
Workbooks(strTab).Worksheets("Zusammenfassung").Activate
Application.Calculate
ThisWorkbook.Worksheets("How-To").Activate
Application.ScreenUpdating = True
End Sub
Thanks in advance