Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: dynamische Tabelle kopieren und einfügen

dynamische Tabelle kopieren und einfügen
28.07.2016 14:22:34
Berndt
Hallo Freunde,
kann mir jmd. mit einem makro weiterhelfen, dass mir folgendes möglich macht.
- ich pflege eine Tabelle (Blatt "Mitarbeiter 3")mit meinen Aufgaben und der dazu benötigten zeit
- das Makro soll mir diese variable Tabelle kopieren und im Blatt "Dashboard" an der dazugehörigen Stelle einfügen
https://www.herber.de/bbs/user/107295.xlsm
Über Hilfe würde ich mich freuen.
VG Berndt
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: dynamische Tabelle kopieren und einfügen
28.07.2016 19:12:53
Bastian
Hey Berndt =D
Ich hatte gerade etwas langeweile ist zwar ziemlich verwirrend aber es geht hoffe ich;)
Einfach in VBa in Dashboard kopieren das Makro vorhher noch die Ziel Tabelle vom Dashboard Löschen dann das Makro starten .
Gruß Basti
Sub DB()
Dim WSd As Worksheet
Dim WSM As Worksheet
Dim LastWsD As Long
Dim LastWsDF As Long
Dim LastWsM As Long
Dim Rng As Range
Dim zell As Range
Dim ZeitGes As Double
Dim HDay As Double
Application.ScreenUpdating = False
Set WSd = ThisWorkbook.Worksheets("Dashboard")
LastWsDF = WSd.Cells(1048576, 5).End(xlUp).Row
If LastWsDF = 4 Then LastWsDF = 5
HDay = 7
With WSd.Range(WSd.Cells(5, 2), WSd.Cells(LastWsDF + 1, 7))
.Clear
.Borders(xlEdgeLeft).ThemeColor = 1
.Borders(xlEdgeTop).ThemeColor = 1
.Borders(xlEdgeBottom).ThemeColor = 1
.Borders(xlEdgeRight).ThemeColor = 1
.Borders(xlInsideVertical).ThemeColor = 1
.Borders(xlInsideHorizontal).ThemeColor = 1
.RowHeight = 12.75
End With
For Each WSM In ThisWorkbook.Worksheets
LastWsD = WSd.Cells(1048576, 5).End(xlUp).Row + 1
LastWsM = WSM.Cells(1048576, 2).End(xlUp).Row
ZeitGes = 0
If WSM.Name Like "Mitarbeiter*" Then
With WSd
.Cells(LastWsD, 3) = WSM.Name
Set Rng = WSM.Range(WSM.Cells(8, 2), WSM.Cells(LastWsM, 3))
Rng.Copy
.Cells(LastWsD, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
For Each zell In .Range(.Cells(LastWsD, 6), .Cells(LastWsD + Rng.Rows.Count, 6))
ZeitGes = ZeitGes + zell
Next
.Cells(LastWsD, 4) = ZeitGes
.Cells(LastWsD, 4).Offset(1, 0) = HDay
If ZeitGes > HDay Then .Cells(LastWsD, 2).Interior.ColorIndex = 3: .Cells(LastWsD, 2) = " _
Achtung: Zu viele Stunden angegeben! Kapazitätsproblem"
If ZeitGes = HDay Then .Cells(LastWsD, 2).Interior.ColorIndex = 27: .Cells(LastWsD, 2) = " _
Anderen Mitarbeitern kann ich behilflich sein. Freie Kapazitäten"
If ZeitGes 

Anzeige
AW: dynamische Tabelle kopieren und einfügen
28.07.2016 19:21:35
Bastian
Ups da war noch ein dreher drinne =D
Sub DB()
Dim WSd As Worksheet
Dim WSM As Worksheet
Dim LastWsD As Long
Dim LastWsDF As Long
Dim LastWsM As Long
Dim Rng As Range
Dim zell As Range
Dim ZeitGes As Double
Dim HDay As Double
Application.ScreenUpdating = False
Set WSd = ThisWorkbook.Worksheets("Dashboard")
LastWsDF = WSd.Cells(1048576, 5).End(xlUp).Row
If LastWsDF = 4 Then LastWsDF = 5
HDay = 7
With WSd.Range(WSd.Cells(5, 2), WSd.Cells(LastWsDF + 1, 7))
.Clear
.Borders(xlEdgeLeft).ThemeColor = 1
.Borders(xlEdgeTop).ThemeColor = 1
.Borders(xlEdgeBottom).ThemeColor = 1
.Borders(xlEdgeRight).ThemeColor = 1
.Borders(xlInsideVertical).ThemeColor = 1
.Borders(xlInsideHorizontal).ThemeColor = 1
.RowHeight = 12.75
End With
For Each WSM In ThisWorkbook.Worksheets
LastWsD = WSd.Cells(1048576, 5).End(xlUp).Row + 1
LastWsM = WSM.Cells(1048576, 2).End(xlUp).Row
ZeitGes = 0
If WSM.Name Like "Mitarbeiter*" Then
With WSd
.Cells(LastWsD, 3) = WSM.Name
Set Rng = WSM.Range(WSM.Cells(8, 2), WSM.Cells(LastWsM, 3))
Rng.Copy
.Cells(LastWsD, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
For Each zell In .Range(.Cells(LastWsD, 6), .Cells(LastWsD + Rng.Rows.Count, 6))
ZeitGes = ZeitGes + zell
Next
.Cells(LastWsD, 4) = ZeitGes
.Cells(LastWsD, 4).Offset(1, 0) = HDay
If ZeitGes > HDay Then .Cells(LastWsD, 2).Interior.ColorIndex = 3: .Cells(LastWsD, 2) = " _
Achtung: Zu viele Stunden angegeben! Kapazitätsproblem"
If ZeitGes = HDay Then .Cells(LastWsD, 2).Interior.ColorIndex = 27: .Cells(LastWsD, 2) = " _
Sie sind ausgelastet"
If ZeitGes 

Anzeige
AW: dynamische Tabelle kopieren und einfügen
29.07.2016 08:59:51
Berndt
Hey Bastian. Deine "Langeweile" rettet mir gerade den Arsch.
Ich prüfe das Makro eben mal durch. Aber dir schon mal tausend Dank. Ich melde mich.
Anzeige
AW: dynamische Tabelle kopieren und einfügen
29.07.2016 14:54:20
Berndt
Du könntest mir nicht zufällig ein Paar Worte dazu sagen, bzw. den Code etwas kommentieren.
Ich versuche Ihn gerade mit hängen und würgen auf mein Bsp. anzupassen. :-)
AW: dynamische Tabelle kopieren und einfügen
29.07.2016 15:16:31
Bastian
Also besser ist es wenn du mir sagst was du willst=D da ich selber erst gucken muss was wie und wo ;)
Aber bis auf die Ampeln ist doch alle so wie in deinem Beispiel ?
Gruß Basti
Anzeige
AW: dynamische Tabelle kopieren und einfügen
01.08.2016 09:34:18
Berndt
Danke vielmals.
Auch ich habe das Makro entsprechend angepasst.
jedoch habe ich noch folgende Verbesserungswünsche, wo mir die Umsetzungsidee fehlt:
- im angehängten Bsp. habe ich das Ampelsystem wieder eingefügt. Dieses bei zu behalten wäre mir wichtig. Die Idee mit dem Text (grün, gelb, rot) ist auch nicht schlecht, jedoch wirkt das Ampelsystem besser auf die Mitarbeiter bzw. dem Gruppenleiter
- bei "Kapazitätsübersicht" werden die Ampeln zur Übersicht reinkopiert für jeden Mitarbeiter.
Das Makro müsste die Ampel so zu sagen noch zusätzlich dort einfügen bzw. verformeln (=Zelle xyz).
- kann man die Summe der Zeiten als Formel hinterlegen? Rechnen tut es ja schon richtig, nur legt es keine Formel ab.
- Die Frage die sich mir stellt, ob es nicht besser ist wenn jeder Mitarbeiter im entsp. Mitarbeiterblatt einen eigenen Button bekommt (wie ich schon angedeutet(oranger Button)) habe , wo nur sein Teil im Dashboard aktualisiert wird.
Weil wenn ich eine Aufgabe zusätzlich nachträglich eintrage, dann muss ja nur mein Teil aktualisiert werden und nicht der von den anderen Kollegen.
https://www.herber.de/bbs/user/107338.xlsm
Würde mich über Hilfe freuen.
VG Berndt
Anzeige
AW: dynamische Tabelle kopieren und einfügen
01.08.2016 13:07:08
baschti007
HEy Berndt
Die Struktur muss immer von anfang an feststehen da sonst alles wieder abgeändert werden muss und man somit doppelt arbeiten muss.
Hab dir das mit den Ampeln noch gemach und der Kapazitäten Übersicht nur kannst du jetzt nicht mehr als 13 Personen anlegen weil mehr wird nicht angezeigt weil sonst die Kapazitäten Übersicht in den Tagesplan gehen würde kann man auch ändern aber damit müsstest du dich dann selber befassen ;) Übung macht den Meister und da ich auch selber nur Google um an mein Ziel zu kommen wirst du das wohl auch schaffen =D
Gruß Basti
https://www.herber.de/bbs/user/107342.xlsm
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Dynamische Tabellen in Excel kopieren und einfügen


Schritt-für-Schritt-Anleitung

Um eine dynamische Tabelle in Excel zu kopieren und im Dashboard einzufügen, kannst Du ein VBA-Makro verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

  1. Öffne Excel und gehe zu dem Arbeitsblatt, in dem Du das Makro verwenden möchtest.
  2. Drücke ALT + F11, um den VBA-Editor zu öffnen.
  3. Erstelle ein neues Modul:
    • Klicke mit der rechten Maustaste auf "VBAProject (DeinDateiname)".
    • Wähle Einfügen > Modul.
  4. Füge den folgenden Code ein:
Sub DB()
    Dim WSd As Worksheet
    Dim WSM As Worksheet
    Dim LastWsD As Long
    Dim LastWsDF As Long
    Dim LastWsM As Long
    Dim Rng As Range
    Dim zell As Range
    Dim ZeitGes As Double
    Dim HDay As Double
    Application.ScreenUpdating = False
    Set WSd = ThisWorkbook.Worksheets("Dashboard")
    LastWsDF = WSd.Cells(1048576, 5).End(xlUp).Row
    If LastWsDF = 4 Then LastWsDF = 5
    HDay = 7
    With WSd.Range(WSd.Cells(5, 2), WSd.Cells(LastWsDF + 1, 7))
        .Clear
        .Borders(xlEdgeLeft).ThemeColor = 1
        .Borders(xlEdgeTop).ThemeColor = 1
        .Borders(xlEdgeBottom).ThemeColor = 1
        .Borders(xlEdgeRight).ThemeColor = 1
        .Borders(xlInsideVertical).ThemeColor = 1
        .Borders(xlInsideHorizontal).ThemeColor = 1
        .RowHeight = 12.75
    End With
    For Each WSM In ThisWorkbook.Worksheets
        LastWsD = WSd.Cells(1048576, 5).End(xlUp).Row + 1
        LastWsM = WSM.Cells(1048576, 2).End(xlUp).Row
        ZeitGes = 0
        If WSM.Name Like "Mitarbeiter*" Then
            With WSd
                .Cells(LastWsD, 3) = WSM.Name
                Set Rng = WSM.Range(WSM.Cells(8, 2), WSM.Cells(LastWsM, 3))
                Rng.Copy
                .Cells(LastWsD, 5).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                For Each zell In .Range(.Cells(LastWsD, 6), .Cells(LastWsD + Rng.Rows.Count, 6))
                    ZeitGes = ZeitGes + zell
                Next
                .Cells(LastWsD, 4) = ZeitGes
                .Cells(LastWsD, 4).Offset(1, 0) = HDay
                If ZeitGes > HDay Then .Cells(LastWsD, 2).Interior.ColorIndex = 3: .Cells(LastWsD, 2) = "Achtung: Zu viele Stunden angegeben! Kapazitätsproblem"
                If ZeitGes = HDay Then .Cells(LastWsD, 2).Interior.ColorIndex = 27: .Cells(LastWsD, 2) = "Sie sind ausgelastet"
            End With
        End If
    Next WSM
    Application.ScreenUpdating = True
End Sub
  1. Schließe den VBA-Editor und gehe zurück zu Excel.
  2. Führe das Makro aus, indem Du ALT + F8 drückst, das Makro auswählst und auf Ausführen klickst.

Häufige Fehler und Lösungen

  • Fehler: Makro läuft nicht
    Lösung: Stelle sicher, dass die Makros in den Excel-Optionen aktiviert sind.

  • Fehler: Keine Daten in Dashboard
    Lösung: Überprüfe, ob die Arbeitsblätter "Mitarbeiter 3" und "Dashboard" existieren und die Daten korrekt eingegeben wurden.

  • Fehler: Ampelsystem funktioniert nicht
    Lösung: Prüfe den Code auf korrekte Zellenreferenzen und stelle sicher, dass die Bedingungen für die Ampeln richtig gesetzt sind.


Alternative Methoden

Wenn Du keine VBA-Makros verwenden möchtest, kannst Du auch folgende Methoden ausprobieren:

  • Kopieren und Einfügen: Manuell die Tabelle kopieren und in das Dashboard einfügen. Dies ist jedoch weniger dynamisch.
  • Power Query: Nutze Power Query, um Daten zu importieren und zu transformieren. Dies ist besonders nützlich für umfangreiche Datenanalysen.

Praktische Beispiele

  1. Beispiel für die Nutzung des Makros:

    • Du hast mehrere Mitarbeiterblätter namens "Mitarbeiter 1", "Mitarbeiter 2", etc. Das Makro kopiert automatisch die Zeiten und Aufgaben in das Dashboard.
  2. Ampelsystem implementieren:

    • Füge eine zusätzliche Bedingung im Makro hinzu, die die Hintergrundfarbe der Zellen basierend auf den Zeiten ändert.

Tipps für Profis

  • Erweiterung des Makros: Du kannst das Makro anpassen, um spezifische Anforderungen zu erfüllen, wie z. B. das Hinzufügen von Buttons für jeden Mitarbeiter.
  • Formeln in Dashboard integrieren: Anstatt nur Werte einzufügen, kannst Du auch Formeln für die Summierung der Zeiten verwenden, um eine automatische Aktualisierung zu gewährleisten.

FAQ: Häufige Fragen

1. Wie kann ich das Makro anpassen?
Du kannst die Variablen und Zellreferenzen im VBA-Code ändern, um das Makro an Deine spezifischen Bedürfnisse anzupassen.

2. Welche Excel-Version benötige ich für VBA?
VBA ist in Excel-Versionen ab 2010 verfügbar. Stelle sicher, dass Du eine unterstützte Version hast.

3. Kann ich das Makro für mehrere Tabellen verwenden?
Ja, das Makro kann leicht angepasst werden, um mehrere Tabellen in einem einzigen Schritt zu verarbeiten.

4. Was muss ich tun, wenn ich Fehler im Makro bekomme?
Überprüfe den Code auf Syntaxfehler und stelle sicher, dass alle verwendeten Blätter und Zellen korrekt angegeben sind.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige