|
Hallo,
ich habe folgendes Problem:
Ich habe mir eine Tabelle angelegt mit allen möglichen Formeln usw. Jetzt möchte ich die Tabelle über ein Makro kopieren, deren Namen ändern und ans Ende der Excel Datei verschieben (soweit kein Problem). Ich möchte das sich eine Formel der neuen Tabelle immer auf die Vorherige Tabelle bezieht.
Bsp: Ich habe die Tabellen "Rechnung vom 06.03.18" und Rechnung vom "15.04.2018"
Jede Tabelle hat ca. 50 Personen mit Vorname Nachname und einem alten und neuen Kontostand.
In der Tabelle "Rechnung vom 15.04.2018"
ist im Feld Kontostand alt der einen Verweis eingebaut hat welcher der Kontostand neu von der Tabelle "Rechnung vom 06.03.2018" ausgibt.
Die Formel sieht so aus:
=VERWEIS(2;1/('Rechnung vom 06.03.18 '!$B$4:$B$99&'Rechnung vom 06.03.18 '!$C$4:$C$99='Rechnung vom 15.04.2018'!B6&'Rechnung vom 15.04.2018'!C6);'Rechnung vom 06.03.18 '!$L$4:$L$99)
Der Verweis vergleicht 2 Krieterien und sucht das resultierende Ergebnis aus einem alten Tabellenblatt:
Jetzt soll mein Makro ergenzent zu oben den Aufgaben noch die Formel soweit automatisch anpassen das sie sich automatisch auf die letze Tabelle bezieht.
Ablauf:
1 Aktuelle Tabelle Kopieren.
2 Neue Tabelle Name ändern: (Rechnung vom 01.05.18)
3 Neue Tabelle nach hinten verschieben.
4 Zur alten Tabelle Springen (die wo kopiert wurde und laut Logik eine Tabelle vor der Aktuellen ist)
5 Name der alten Tabelle kopieren.
6 Zur neuen Tabelle springen.
7 In der Formel an der richtigen stelle der Name der neuen Tabelle einfügen.
Fertig
Die ersten drei Punkte funktionieren:
Option Explicit
Sub BlattKopieren()
'
' Tabellenblatt kopieren und umbenennen
'
Dim strName As String
strName = InputBox("Name des neuen Blatts:", "Blatt benennen")
If strName = "" Then
MsgBox "Leider wurde kein Blattname eingetragen!"
Exit Sub
End If
Sheets("Rechnung vom 15.04.2018").Copy After:=Sheets("Rechnung vom 15.04.2018")
ActiveSheet.Name = strName
ActiveSheet.Range("A2") = strName
MsgBox "Blatt erfolgreich kopiert!"
End Sub
'Auf neues Tabellenblatt springen
Sub TabellenblattAktivieren()
Worksheets("strName").Select
End Sub
'Das neue Tabellenblatt nach hinten verschieben
Sub TabellenblattVerschieben()
ActiveSheet.Move After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
End Sub
4,5 und 6 müssten auch stimmen
Sub FormelAendern()
'Auf Voriges Tabellenblatt springen
On Error Resume Next
ActiveSheet.Previous.Activate
On Error Resume Next
'Name des Tabellenblattes kopieren
Sheets("Rechnung vom 06.03.18 ").Select
Range("A2").Select
Selection.Copy
' Wieder auf neues Tabellenblatt springen
Sub TabellenblattAktivieren2()
Worksheets("strName").Select
7 nicht ....
' Formel anpassen
Range("I5").Select
ActiveCell.FormulaR1C1 = _
"=LOOKUP(2,1/('paste'!R4C2:R49C2&'paste'!R4C3:R49C3='strName'!RC[-7]&'strName'!RC[-6]),'paste'!R5C12:R49C12)"
Range("I5").Select
Selection.Copy
Range("I6:I49").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
|