Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1460to1464
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Command button mit Makro erstellen
07.12.2015 16:04:53
Martin
Hallo zusammen,
ich arbeite an einem ziemlich großen Finanzfile und hoffe ihr könnt mir helfen. Und zwar geht es um ein Registerblatt "Verbindlichkeiten" in das alle Eingangsrechnungen eingetragen werden. Die Tabelle beginnt ab der 8. Zeile. Ich möchte einen Command button erstellen (Name: CAPEX ausbuchen), der alle Zeilen der Tabelle, die in der Spalte "L" das Wort "CAPEX" enthalten, ausschneidet und diese Zeilen in das Registerblatt "CAPEX" einfügt (Tabelle beginnt ebenfalls ab Zeile 8 und ist identisch). Anschließend sollen im Registerblatt "CAPEX" die Daten, die in den Spalten "E" und "F" enthalten sind in die Spalten "N" und "O" verschoben werden (Daten aus "E" nach "O"; Daten aus "F" nach "N"). Ist das ganze mit einem Comand-button bzw einem Makro möglich?
Über Lösungsvorschläge, wie das zu bewältigen ist, würde ich mich sehr freuen.
Danke schon mal im Voraus und Beste Grüße
Martin

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Command button mit Makro erstellen
07.12.2015 16:26:29
Sepp
Hallo Martin,
lade eine Beispieldatei mit ein paar Dummy-Daten hoch.Am Beispiel sollte man erkennen, an welcher Spalte man die letzte Datenzeile erkennen kann.
Gruß Sepp

AW: Command button mit Makro erstellen
08.12.2015 13:14:41
Martin
Hallo Sepp,
danke für deine schnelle Antwort und die Bereitschaft mir zu helfen :). Ich habe eine Beispieldatei hochgeladen. Diese enthält jedoch nur die 2 betroffenen Registerblätter (Verbindlichkeiten & CAPEX). Die Originaldatei enthält noch eine ganze Reihe anderer Registerblätter. Ich hoffe die Beispieldatei hilft trotzdem bei der Lösung.
Hier der Link der Beispieldatei: https://www.herber.de/bbs/user/102098.xlsx
Beste Grüße
Martin

Anzeige
AW: Command button mit Makro erstellen
08.12.2015 13:33:01
Sepp
Hallo Martin,
weise folgenden Code einer Schaltfläche zu.
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub capex()
Dim rng As Range
Dim lngNext As Long, lngR As Long
Dim CalculationMode As Long

On Error GoTo ErrorHandler

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  CalculationMode = .Calculation
  .Calculation = xlManual
  .DisplayAlerts = False
End With

With Sheets("Verbindlichkeiten").Range("A7").CurrentRegion
  .AutoFilter Field:=12, Criteria1:="=CAPEX"
  On Error Resume Next
  Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
End With

If Not rng Is Nothing Then
  With Sheets("CAPEX")
    lngNext = Application.Max(8, .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
    rng.Copy .Cells(lngNext, 1)
    For lngR = lngNext To Application.Max(lngNext, .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
      .Cells(lngR, 15) = .Cells(lngR, 5).Value: .Cells(lngR, 5) = ""
      .Cells(lngR, 14) = .Cells(lngR, 6).Value: .Cells(lngR, 6) = ""
    Next
    rng.Delete
  End With
End If

Sheets("Verbindlichkeiten").Range("A7").CurrentRegion.AutoFilter

ErrorHandler:

With Err
  If .Number <> 0 Then
    MsgBox "Fehler in Prozedur:" & vbTab & "'nn'" & vbLf & String(25, "—") & _
      vbLf & vbLf & IIf(Erl, "Fehler in Zeile:" & vbTab & Erl & vbLf & vbLf, "") & _
      "Fehlernummer:" & vbTab & .Number & vbLf & vbLf & "Beschreibung:" & vbTab & _
      .Description & vbLf, 81968, "VBA - Fehler in Prozedur - capex", .HelpFile, .HelpContext
    .Clear
  End If
End With

On Error GoTo 0

With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .Calculation = CalculationMode
  .DisplayAlerts = True
  .StatusBar = False
End With

End Sub

Gruß Sepp

Anzeige
AW: Command button mit Makro erstellen
08.12.2015 15:05:41
Martin
Hallo Sepp,
Danke für den Code. Leider funktioniert es nicht wie es soll. Es springt lediglich ein Fehlerkästchen auf. Folgendes steht drin:
Fehler in Prozedur: 'nn'
Fehlernummer: 1004
Beschreibung: Die AutoFilter-Methode des Range-Objektes konnte nicht ausgeführt werden.
Meine Vorgängerin hat schonmal ein ähnliches Makro im Registerblatt Verbindlichkeiten eingebaut, das ohne Probleme funktioniert. Es verschiebt alle Zeilen, die ein Datum in der Spalte "bezahlt am" stehen haben in ein anderes Registerblatt. Das ist der Code dazu:
Private Sub CommandButton4_Click()
CommandButton4.Caption = "als bezahlt ausbuchen"
Dim rngC As Range, rngA As Range
For Each rngC In Range("i8", Cells(Rows.Count, 9).End(xlUp))
If rngC.Row > 1 And IsDate(rngC.Value) Then
If rngA Is Nothing Then Set rngA = rngC Else Set rngA = Union(rngA, rngC)
End If
Next rngC
If Not rngA Is Nothing Then
With Worksheets("Rechnungenbezahlt")            ' Zieltabelle
rngA.EntireRow.Copy .Cells(.Rows.Count, 9).End(xlUp).Offset(1, -8)
rngA.EntireRow.Delete
End With
End If
End Sub

Vielleicht kann man diesen Code hernehmen und lediglich umschreiben sodass die CAPEX ausgebucht und anschließend noch die Spalten vertauscht werden? Ich kenne mich da leider nicht gut genug damit aus...
Grüße
Martin

Anzeige
AW: Command button mit Makro erstellen
08.12.2015 15:11:57
Sepp
Hallo Martin,
ich brauch kein Makro als Vorlage.
Hast du einen Blattschutz drin?
Gruß Sepp

AW: Command button mit Makro erstellen
08.12.2015 15:29:17
Martin
Hi Sepp,
nein ich habe keinen Blattschutz drin...
Grüße
Martin

AW: Command button mit Makro erstellen
08.12.2015 15:44:50
Sepp
Hallo Martin,
also in deiner Datei funktioniert es tadellos!
https://www.herber.de/bbs/user/102112.xlsm
Gruß Sepp

Anzeige
AW: Command button mit Makro erstellen
08.12.2015 16:03:27
Martin
Hallo Sepp,
du hast recht, da funktioniert es. Nur werden die Daten aus den Spalten "Brutto" und "Netto" nicht in die Spalten "Anschaffungskosten brutto" und "Anschaffungskosten netto" übertragen. Wenn das noch klappt wär es super! :)
In der Zwischenzeit schau ich mal woran es liegt, dass es im Original-File nicht funktioniert.
Grüße
Martin

AW: Command button mit Makro erstellen
08.12.2015 16:05:28
Sepp
Hallo Martin,
doch werden sie schon, bei deiner Datei hab ich mehrfach die Einträge zurückkopiert und dabei vergessen die Spaltenwerte wieder zu tauschen.
Gruß Sepp

Anzeige
AW: Command button mit Makro erstellen
08.12.2015 16:35:26
Martin
Hallo Sepp,
stimmt, in der Beispieldatei funktioniert es perfekt. Bei mir kommt leider immernoch nur der Fehler...
Meinst du es könnte daran liegen, dass bei mir im Modul 1 noch ein anderes Makro namens konsolidieren aktiv ist. Dieses dient dazu, alle Daten, die in den Registerblättern "Verbindlichkeiten", "Rechnungen bezahlt" und "CAPEX" enthalten sind in einem extra Blatt "Konsolidierung Kosten" zusammenzuführen.
Hier der Code:
Sub Konsolidieren()
Dim i As Long
Worksheets("Konsolidierung Kosten").Activate
Range("A2:K5000").Select
Selection.ClearContents
Range("A1").Select
Dim Zeile&, letzteZ&
'Von Blatt 9 bis Blatt 11 zusammenfassen
For i = 4 To 6
With Worksheets(i)
'Von Zeile 2 bis zur vorletzten kopieren nach ZMEK
letzteZ = .Cells(Rows.Count, 1).End(xlUp).Row
Zeile = Worksheets("Konsolidierung Kosten").Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("A8:P" & letzteZ).Copy Worksheets("Konsolidierung Kosten").Range("A" & Zeile) _
End With
Next
Calculate
Worksheets("GuV").Activate
End Sub
Vielen Dank nochmal für deine Hilfe!
Grüße
Martin

Anzeige
AW: Command button mit Makro erstellen
08.12.2015 16:51:42
Martin
Hallo Sepp,
mir ist aufgefallen, wenn ich deinen Code in meine Datei Kopiere und anschließend im Tabellenblatt GuV auf Aktualisieren drücke, dann zerschießt es mir die Komplette GuV Pivottabelle. Diese Pivottabelle bezieht ihre Daten aus dem Blatt "Konsolidierung Kosten". Statt alle Kosten für die einzelnen Monate des Jahres anzuzeigen (wie normal), kommt dann eine Tabelle mit einer extra Spalte für jeden Kostenpunkt.
Gibt es noch eine andere Art die Capex ausbuchen zu lassen? (anderer Code)
Grüße
Martin

AW: Command button mit Makro erstellen
08.12.2015 19:23:44
Sepp
Hallo Martin,
anderer Code, ja, aber ob das Ergebnis dann ein anderes ist, ist fraglich.
Außerdem hat mein Code mit dem Blatt "Konsolidierung Kosten" nichts zu tun.
Gruß Sepp

Anzeige
AW: Command button mit Makro erstellen
09.12.2015 10:21:16
Martin
Guten Morgen Sepp,
ich habe jetzt endlich herausgefunden woran es lag, dass dein Code nicht funktioniert hat. Im Blatt "Verbindlichkeiten" darf kein Filter aktiviert sein sonst funktioniert es nicht. Vielen Dank für den super Code! :) Jetzt geht es endlich.
Eine Frage hätte ich noch: Da im Blatt "Verbindlichkeiten" meistens der Filter aktiv ist und sehr oft genutzt wird (auch von Kollegen), ist es vll möglich ein Makro mit dem gleichen Effekt zu schreiben, nur so, dass der Filter auch aktiv sein darf?
Beste Grüße und nochmals Vielen Dank
Martin

AW: Command button mit Makro erstellen
09.12.2015 18:29:18
Sepp
Hallo Martin,
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub capex()
Dim rng As Range
Dim lngNext As Long, lngR As Long
Dim CalculationMode As Long

On Error GoTo ErrorHandler

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  CalculationMode = .Calculation
  .Calculation = xlManual
  .DisplayAlerts = False
End With

With Sheets("Verbindlichkeiten").Range("A7").CurrentRegion
  If .Parent.AutoFilterMode Then .Parent.ShowAllData
  .AutoFilter Field:=12, Criteria1:="=CAPEX"
  On Error Resume Next
  Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
End With

If Not rng Is Nothing Then
  With Sheets("CAPEX")
    lngNext = Application.Max(8, .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
    rng.Copy .Cells(lngNext, 1)
    For lngR = lngNext To Application.Max(lngNext, .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
      .Cells(lngR, 15) = .Cells(lngR, 5).Value: .Cells(lngR, 5) = ""
      .Cells(lngR, 14) = .Cells(lngR, 6).Value: .Cells(lngR, 6) = ""
    Next
    rng.Delete
  End With
End If

Sheets("Verbindlichkeiten").Range("A7").CurrentRegion.AutoFilter

ErrorHandler:

With Err
  If .Number <> 0 Then
    MsgBox "Fehler in Prozedur:" & vbTab & "'nn'" & vbLf & String(25, "—") & _
      vbLf & vbLf & IIf(Erl, "Fehler in Zeile:" & vbTab & Erl & vbLf & vbLf, "") & _
      "Fehlernummer:" & vbTab & .Number & vbLf & vbLf & "Beschreibung:" & vbTab & _
      .Description & vbLf, 81968, "VBA - Fehler in Prozedur - capex", .HelpFile, .HelpContext
    .Clear
  End If
End With

On Error GoTo 0

With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .Calculation = CalculationMode
  .DisplayAlerts = True
  .StatusBar = False
End With

End Sub

Gruß Sepp

Anzeige
AW: Command button mit Makro erstellen
10.12.2015 17:46:59
Martin
Hallo Sepp,
leider funktioniert es immer noch nicht wenn der Filter aktiv ist. Ich glaube es liegt an der Auto-Filter-Funktion im Code.
Mein Chef meinte heute auch, dass es mit aktivem Filter funktionieren muss, weil die Kollegin (welche hauptsächlich mit dem File arbeitet) ausschließlich mit dem Filter arbeitet um einen Überblick über die sehr vielen Daten zu erhalten. Kannst du mir da weiterhelfen?
Vielen Dank im Voraus
Beste Grüße
Martin

AW: Command button mit Makro erstellen
10.12.2015 17:48:38
Martin
Hallo Sepp,
leider funktioniert es immer noch nicht wenn der Filter aktiv ist. Ich glaube es liegt an der Auto-Filter-Funktion im Code.
Mein Chef meinte heute auch, dass es mit aktivem Filter funktionieren muss, weil die Kollegin (welche hauptsächlich mit dem File arbeitet) ausschließlich mit dem Filter arbeitet um einen Überblick über die sehr vielen Daten zu erhalten. Kannst du mir da weiterhelfen?
Vielen Dank im Voraus
Beste Grüße
Martin

Anzeige
AW: Command button mit Makro erstellen
10.12.2015 17:49:25
Martin
Hallo Sepp,
leider funktioniert es immer noch nicht wenn der Filter aktiv ist. Ich glaube es liegt an der Auto-Filter-Funktion im Code.
Mein Chef meinte heute auch, dass es mit aktivem Filter funktionieren muss, weil die Kollegin (welche hauptsächlich mit dem File arbeitet) ausschließlich mit dem Filter arbeitet um einen Überblick über die sehr vielen Daten zu erhalten. Kannst du mir da weiterhelfen?
Vielen Dank im Voraus
Beste Grüße
Martin

AW: Command button mit Makro erstellen
10.12.2015 17:49:25
Martin
Hallo Sepp,
leider funktioniert es immer noch nicht wenn der Filter aktiv ist. Ich glaube es liegt an der Auto-Filter-Funktion im Code.
Mein Chef meinte heute auch, dass es mit aktivem Filter funktionieren muss, weil die Kollegin (welche hauptsächlich mit dem File arbeitet) ausschließlich mit dem Filter arbeitet um einen Überblick über die sehr vielen Daten zu erhalten. Kannst du mir da weiterhelfen?
Vielen Dank im Voraus
Beste Grüße
Martin

AW: Command button mit Makro erstellen
10.12.2015 17:55:52
Martin
Hallo Sepp,
entschuldige die vielen Antworten. Mein explorer ist beim hochladen der Antwort hängen geblieben und nun hat die Antwort scheinbar öfter hochgeladen.
Noch zu Info. Bei dem aktuellen Code kommt folgende Fehlermeldung:
"Die ShowAllData-Methode des Worksheet-Objektes konnte nicht ausgeführt werden."
Grüße
Martin

AW: Command button mit Makro erstellen
10.12.2015 19:01:11
Sepp
Hallo Martin,
also bei mir funktioniert der Code, egal ob gefiltert oder nicht.
Beim folgenden Code, wird ein evtl. gesetzter Filter, nach der Ausführung wieder aktiviert.
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub capex()
Dim rng As Range
Dim lngNext As Long, lngR As Long
Dim CalculationMode As Long

Const cstrView As String = "tempView"

On Error GoTo ErrorHandler

With Application
  .ScreenUpdating = False
  .EnableEvents = False
  CalculationMode = .Calculation
  .Calculation = xlManual
  .DisplayAlerts = False
End With

ThisWorkbook.CustomViews.Add cstrView, True, True

With Sheets("Verbindlichkeiten").Range("A7").CurrentRegion
  .AutoFilter
  .AutoFilter Field:=12, Criteria1:="=CAPEX"
  On Error Resume Next
  Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(xlCellTypeVisible)
  On Error GoTo 0
End With

If Not rng Is Nothing Then
  With Sheets("CAPEX")
    lngNext = Application.Max(8, .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
    rng.Copy .Cells(lngNext, 1)
    For lngR = lngNext To Application.Max(lngNext, .Cells(.Rows.Count, 1).End(xlUp).Row + 1)
      .Cells(lngR, 15) = .Cells(lngR, 5).Value: .Cells(lngR, 5) = ""
      .Cells(lngR, 14) = .Cells(lngR, 6).Value: .Cells(lngR, 6) = ""
    Next
    rng.Delete
  End With
End If

Sheets("Verbindlichkeiten").Range("A7").CurrentRegion.AutoFilter

With ThisWorkbook
  .CustomViews(cstrView).Show
  .CustomViews(cstrView).Delete
End With

ErrorHandler:

With Err
  If .Number <> 0 Then
    MsgBox "Fehler in Prozedur:" & vbTab & "'nn'" & vbLf & String(25, "—") & _
      vbLf & vbLf & IIf(Erl, "Fehler in Zeile:" & vbTab & Erl & vbLf & vbLf, "") & _
      "Fehlernummer:" & vbTab & .Number & vbLf & vbLf & "Beschreibung:" & vbTab & _
      .Description & vbLf, 81968, "VBA - Fehler in Prozedur - capex", .HelpFile, .HelpContext
    .Clear
  End If
End With

On Error GoTo 0

With Application
  .ScreenUpdating = True
  .EnableEvents = True
  .Calculation = CalculationMode
  .DisplayAlerts = True
  .StatusBar = False
End With

End Sub

Gruß Sepp

AW: Command button mit Makro erstellen
11.12.2015 13:25:42
Martin
Hi Sepp,
vielen vielen Dank! Jetzt funktioniert es perfekt :)
Ich hätte noch eine weitere Frage: Wäre es möglich den Code so zu erweitern, dass jede Zeile, die nach Capex ausgebucht wird insgesamt 12 Mal im Tabellenblatt Capex angezeigt wird? Quasi alles wie bisher, nur dass jede Zeile 11 weitere Mal angezeigt/kopiert wird?
Das wäre echt super. Im Blatt Capex sollen nähmlich die Monatlichen Abschreibungsbeträge errechnet werden, und damit der Monatsbetrag in jedem Monat in der GuV angezeigt wird brauche ich jeden Posten 12 Mal (mit verschiedenen Datumswerten in der Spalte "Eingangsdatum"; auf diese bezieht sich nämlich die GuV).
Ich habe eine Beispieldatei hochgeladen, damit du weißt wie ich es meine:
https://www.herber.de/bbs/user/102184.xlsx
Vll kann man den Code ja sogar so schreiben, dass alle Formeln, die im Blatt Capex enthalten sind später auch automatisch so übernommen werden?
Viele Grüße
Martin

AW: Command button mit Makro erstellen
11.12.2015 16:08:25
Sepp
Hallo Martin,
schau ich mir an, aber erst Morgen oder am Sonntag.
Gruß Sepp

AW: Command button mit Makro erstellen
10.12.2015 17:49:26
Martin
Hallo Sepp,
leider funktioniert es immer noch nicht wenn der Filter aktiv ist. Ich glaube es liegt an der Auto-Filter-Funktion im Code.
Mein Chef meinte heute auch, dass es mit aktivem Filter funktionieren muss, weil die Kollegin (welche hauptsächlich mit dem File arbeitet) ausschließlich mit dem Filter arbeitet um einen Überblick über die sehr vielen Daten zu erhalten. Kannst du mir da weiterhelfen?
Vielen Dank im Voraus
Beste Grüße
Martin

AW: Command button mit Makro erstellen
10.12.2015 17:49:26
Martin
Hallo Sepp,
leider funktioniert es immer noch nicht wenn der Filter aktiv ist. Ich glaube es liegt an der Auto-Filter-Funktion im Code.
Mein Chef meinte heute auch, dass es mit aktivem Filter funktionieren muss, weil die Kollegin (welche hauptsächlich mit dem File arbeitet) ausschließlich mit dem Filter arbeitet um einen Überblick über die sehr vielen Daten zu erhalten. Kannst du mir da weiterhelfen?
Vielen Dank im Voraus
Beste Grüße
Martin

306 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige