AW: Kopierte Mappen umbenennen
09.02.2021 10:21:27
Stefan
Hi Piet,
ich muß über Deine Antwort sehr schmunzeln! Danke für die BLumen.... Ja, ich bin ein schlechter VBA-Programmierer. :) Deswegen schicke ich Dir mal mein komplettes Programm. Was noch nicht funktioniert, ist, daß die Mappe3 bzw. Mappe"Muster" kopiert werden soll und die Kopie die Variable a erhalten soll. Dies soll sooft passieren, wie die Schleife läuft.
Sub Zaehlen()
Dim a As String
Dim b As String
Dim text As String
Dim i As Integer, n As Integer
Dim XY As String
Dim WS As Worksheet
Dim DeinNeuerName As String
Sheets("SaP500").Select
Worksheets("SaP500").Range("T1:T852").Activate
For i = 1 To Cells(Rows.Count, 20).End(xlUp).Row
If Not IsEmpty(Cells(i, 20)) Then
ActiveSheet.Cells(i, 20).Activate
XY = ActiveCell.Value
a = Cells(ActiveCell.Row, 1)
b = Cells(ActiveCell.Row + 1, 1)
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = a
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 1).Value = b
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Value = "XYZ" & _
a & "!B7"
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 3).Value = "XYZ" & _
a & "!C7"
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 4).Value = "XYZ" & _
a & "!D7"
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 5).Value = "XYZ" & _
a & "!E7"
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 6).Value = "XYZ" & _
a & "!F7"
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 7).Value = "XYZ" & _
a & "!G7"
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).Value = _
Worksheets("SaP500").Cells(i, 27)
Worksheets("Vergleich").Cells(Rows.Count, 1).End(xlUp).Offset(0, 8).Value = text
'Cells(Rows.Count, 1).End(xlUp).Offset(1, 3).Value.FormulaLocal = "=" & a & "!C7"
'Cells(Rows.Count, 1).End(xlUp).Offset(1, 4).Value.FormulaLocal = "=" & a & "!D7"
'Cells(Rows.Count, 1).End(xlUp).Offset(1, 5).Value.FormulaLocal = "=" & a & "!E7"
'Cells(Rows.Count, 1).End(xlUp).Offset(1, 6).Value.FormulaLocal = "=" & a & "!F7"
'Cells(Rows.Count, 1).End(xlUp).Offset(1, 7).Value.FormulaLocal = "=" & a & "!G7"
MsgBox "Kontrolle"
Sheets("Muster").Select
Worksheets("Muster").Cells(1, 2).Value = a
Worksheets("Muster").Cells(2, 2).Value = b
Worksheets("Muster").Cells(3, 2).Value = text
Sheets("Muster").Select
Sheets("Muster").Activate
ThisWorkbook.Worksheets("Muster").Copy After:=ThisWorkbook.Sheets(Sheets.Count)
ActiveSheet.Name = DeinNeuerName
DeinNeuerName = a
n = Active.Sheet.Index
For Each WS In Worksheets
Sheets("Muster").Active
Zellenname = Range("A1")
WS.Name = Zellenname
n = n + 1
Next WS
Else
End If
Next i
End Sub