Zelleninhalt, samt Formatierung etc. in Variable kopieren um später wieder einfügen zu können... - Laufzeitfehler 91

  • Excel

Es gibt 12 Antworten in diesem Thema. Der letzte Beitrag () ist von ereza.

    Zelleninhalt, samt Formatierung etc. in Variable kopieren um später wieder einfügen zu können... - Laufzeitfehler 91

    Hallo liebe Community!

    Ich möchte gerne einen Bereich in einem Tabellenblatt kopieren und dann später 1 zu 1 (mit sämtlichen Formeln, Werten und Formattierungen) zu einem beliebigen Zeitpunkt wieder einfügen lassen. Diese Informationen sollen in der Zwischenzeit in einer Variable gespeichert werden, da ich nicht unmittelbar wieder einfüge sondern einige andere Befehle, ausführen lasse....

    Nun wäre mein bisher produzierter Code so etwas:

    Visual Basic-Quellcode

    1. Sub CommandButton1_Click()
    2. Dim rng As Range
    3. Dim r As Long
    4. r = Cells(Rows.Count, 1).End(xlUp).Row
    5. rng = Range(Cells(1, 1), Cells(r, 7)).Copy
    6. ' Hier stehen dann andere Aufgaben, die erfüllt werden müssen....
    7. '......
    8. '......
    9. '......
    10. '......
    11. '......
    12. '......
    13. '......
    14. Range("A1") = rng.PasteSpecial xlPasteAll
    15. End Sub


    Ich bin mir beim "einfügen" noch nicht sicher wie ich das synthax mäßig richtig schreiben muss... denn ich will wieder an der gleichen Postion, ab Cells(1,1), meinen gespeicherten Inhalt von der Variable rng wieder einfügen....


    Ich komme allerdings nicht wirklich weit... da ich einen Laufzeitfehler bekomme wenn ich das Programm ausführen möchte...:

    Laufzeitfehler 91:
    Die Objektvariable oder die With-Blockvariable wurde nicht festgelegt

    Ich muss da also eine Objektvariable deklarieren und einbauen... mir ist aber trotz recherche leider nicht ganz klar... wie ich das in meinem fall nun machen müsste...
    und... dann...
    ist mir auch noch nicht klar... ob mein bisheriger Ansatz, den ich habe, so, wie ich es umsetzen möchte, überhaupt zielerfüllend ist... ?(

    Hat jemand von euch eine Idee, wie ich das machen müsste??

    LG Tim

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „ereza“ ()

    Hi,

    du versuchst eine Variable zu erstellen und diese gleich zu kopieren.
    Richtig wäre:

    Quellcode

    1. Set rng = Range(Cells(1, 1), Cells(r, 7))
    2. rng.Copy

    Und beim einfügen machst du auch einen Fehler, das müsste so aussehen:

    Quellcode

    1. ​Range("A1").PasteSpecial


    Allerdings verstehe ich nicht wieso du erst A1 bis H? kopierst, und diese dann in A1 wieder einfügen willst.
    Damit würdest du den vorigen Bereich überschreiben.

    Gruß
    Thomas
    Hallo!

    Also der erste Teil... ist mir jz klar...

    Set rng = Range(Cells(1, 1), Cells(r, 7))
    rng.Copy


    jedoch der zweite Teil:
    Range("A1").PasteSpecial


    Da möchte ich ja explizit den Inhalt von der Variable "rng" ab A1 einfügen....

    Allerdings verstehe ich nicht wieso du erst A1 bis H? kopierst, und diese dann in A1 wieder einfügen willst.
    Damit würdest du den vorigen Bereich überschreiben.


    Dieser Bereich wird später gelöscht... heißt... da steht nix mehr da... und ich möchte nachher dann eben genau ab A1, alles das was in rng gespeichert wurde (Formeln, Werte und deren Formatierungen etc.) dort wieder einfügen lassen!

    Deshalb brauche ich eine Methode, wo ich meinen Informationsinhalt in einer Variablen "sichern" kann, sodass ich später drauf zurückgreifen kann und damit arbeiten kann... Das ist eigentlich der Konsens aus dem Ganzen...

    LG Tim
    Ok....

    aber eine Frage...

    durch:

    Set rng = Range(Cells(1, 1), Cells(r, 7))

    wird ja lediglich ein Bereich einer Variablen zugeordnet... oder?

    und... wenn ich dann später sage rng.Copy Range("A1") , dann kopiert der mir alles vom festgelegten Bereich und fügt es mir nach A1 ein??

    Habe ich das richtig verstanden?

    ABER... Das Problem, das ich habe, ist ja dann, dass ich zu dem Zeitpunkt wo ich rng.Copy Range("A1") sage... ja leider nix mehr in dem Bereich stehen habe....! Das ist ja das Dilemma... sonst könnte ich einfach mit copy arbeiten... das ist mir schon klar vom Prinzip her... aber... in meinem Fall, muss ich wirklich diesen Inhalt zuerst "sichern" um diesen dann später, beliebig aus der "Sicherung" abrufen zu können....
    Somit... diesen Inhalt muss ich dann höchstwahrscheinlich mit PasteSpecial xlPasteAll wieder einfügen... da ich alles (also Formeln, Werte & Formatierungen) einfügen möchte...
    Sorry stand voll auf dem Schlauch =)

    Dann könnte man es zb so machen:

    Quellcode

    1. ​Sub CommandButton1_Click()
    2. Dim rng As Range
    3. Dim r As Long
    4. Dim wksa As Worksheet
    5. Dim wksn As Worksheet
    6. Set wksa = ActiveSheet
    7. Set wksn = Worksheets.Add
    8. r = wksa.Cells(Rows.Count, 1).End(xlUp).Row
    9. Set rng = wksa.Range(wksa.Cells(1, 1), wksa.Cells(r, 7))
    10. rng.Copy
    11. wksn.Cells(1, 1).PasteSpecial
    12. ' Hier stehen dann andere Aufgaben, die erfüllt werden müssen....
    13. '......
    14. '......
    15. '......
    16. '......
    17. '......
    18. '......
    19. '......
    20. wksn.UsedRange.Copy wksa.Cells(1, 1)
    21. Application.DisplayAlerts = False
    22. wksn.Delete
    23. Application.DisplayAlerts = False
    24. End Sub


    Gruß
    Thomas
    ok, danke für diesen Ansatz...

    Aber leider... ist das für mich ausgerechnet so nicht wirklich brauchbar... weil...
    Die "anderen" Aufgaben... die erfüllt werden... sind eigentlich genau sowas... dass Inhalte von den jweils vorkommenden Blättern einfach in ein Tab zwischen-übertragen werden und dann in eine neue Arbeitsmappe kopiert werden. und dieses zwischentab dann wieder gelöscht wird.... Dabei werden die Einträge aber noch bearbeitet....
    Bei der Bearbeitung allerdings, überschreibt es mir genau mein Tabellenblatt wo ich den code ausführe... da dieses ursprünglich Verlinkungen auf Zellen die in einer Pivot-Tabelle hat...

    so... jedenfalls...
    Ist das alles nicht weiter schlimm... da ich mir gedacht habe...
    Na gut, dann mach ichs einfach so, dass bevor dieser ganze Vorgang passiert ich einfach die Inhalte von diesem Bereich sichere... und dann später wieder einfüge...

    Somit, danke für deine Idee - und die würde auch im "Normalfall" super hinhauen! - ABER ausgerechnet in meinem Fall ist genau diese Methode nicht brauchbar...

    Die einzige Methodik die ich wirklich gebrauchen kann, damit das alles funktioniert, so wie's soll... ist wirklich, dass ich den Inhalt von einem gewissen Range-Bereich in einer Variable oder Array oder ich weiß nicht was... hinterlege... - aber es muss eben eine Variable oder eben ein Element sein, dem Daten zugeordnet werden können.... und dann später muss ich das wieder abrufen und weiterbearbeiten können!

    Somit... ich denke... ich bräuchte einfach nur die Synthax wie ich:
    1.) den Inhalt (Formeln, Werte mit deren Formatierungen) der in dem Bereich: Range(Cells(1, 1), Cells(r, 7)) liegt kopieren kann und einer Variablen zuordnen kann, sodass der gesamte Inhalt gespeichert ist.
    und
    2.) ich diesen Inhalt von dieser Variablen abrufen kann und per .PasteSpecial xlPasteAll wieder ab Zelle A1 einfügen kann

    LG Tim
    Sorry deine Erklärung, wieso es nicht funktioniert, habe ich nicht verstanden =).
    Mit dem Code wird deine Range kopiert.
    Es wird ein ganz neues Blatt erstellt.
    Dort wird deine Range eingefügt.
    Und Später werden diese Daten wieder ins Ursprungsblatt ab Zelle A1 eingefügt.
    Zuletzt wird das Hilfsblatt wieder gelöscht.

    Im weitesten Sinne könnte man sagen, dass das Hilfsblatt zu deiner Variable wird.
    Ansonsten bleibt nur noch die Möglichkeit ein Array zu befüllen und es später wieder auszulesen.
    Damit arbeite ich allerdings zu selten um dir die richtige Lösung geben zu können.

    Gruß
    Thomas
    @TommyDerWalker:

    Entschuldige bitte... ich denke ich hab mich da wirklich kompliziert ausgedrückt...

    Was ich sagen wollte war...
    Genau das, was du jz genannt hast:
    Mit dem Code wird deine Range kopiert.
    Es wird ein ganz neues Blatt erstellt.
    Dort wird deine Range eingefügt.
    Und Später werden diese Daten wieder ins Ursprungsblatt ab Zelle A1 eingefügt.
    Zuletzt wird das Hilfsblatt wieder gelöscht.

    Im weitesten Sinne könnte man sagen, dass das Hilfsblatt zu deiner Variable wird.


    Genau das habe ich bereits schon für alle Tabellenblätter die in meiner Arbeitsmappe vorkommen NUR, dass schlussendlich, diese dann in eine neue Datei kopiert werden! Aber die Methode, wie diese Tabellenblätter kopiert werden, beruht auf dem selben Prinzip wie deine!
    Wenn ich nun also, deine Methode erneut anwenden würde, so würde mir dieses "Hilfstabellenblatt" ungewollter weise mitkopiert werden, was nicht sein soll... Deshalb bring mir das nix...

    Ich denke in dem Fall würde dann, wie du schon meintest, nur mehr die Möglichkeit bleiben mit einem Array zu arbeiten... aber da ich ein VBA-Pfosten bin und gerade mal ein paar Basics angefangen habe zu begreifen... weiß ich leider nicht wie ich das umsetzen müsste... :((

    ABER ich danke dir vielmals für deine Mühe und deinen Input! Dadurch bin ich jz auch schon gscheider geworden...
    Es ist kein Problem nur die Werte oder die Formeln eines Range in eine Variant-Array zu speichern.

    Visual Basic-Quellcode

    1. Dim rng As Range, arr() As Variant
    2. Set rng = UsedRange
    3. arr = rng.Value
    4. 'alternativ:
    5. arr = rng.Formula


    Mit Set rng=UsedRange speicherst du nur einen Pointer auf den Bereich ab.
    Wenn sich der Inhalt des Bereichs ändert, kriegst du die Änderungen auch in rng.

    Überleg dir, ob es dir nicht genügt, die Werte oder die Formeln zu speichern.
    Wenn du sie nachher an dieselbe Position wieder zurückschreibst, hast du ja die Formate wieder.
    Formate in Excel sind sehr umfangreich und ziehen sich durch viele Properties des Range-Objekts.
    Wenn du tatsächlich alles speichern willst, ist es das einfachste, du kopierst den Range auf ein anderes Arbeitsblatt weg.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    ​Wenn ich nun also, deine Methode erneut anwenden würde, so würde mir dieses "Hilfstabellenblatt" ungewollter weise mitkopiert werden

    Ich kenne deinen Code zwar nicht, aber normalerweise sollte es kein Problem sein das Hilfsblatt (das ja einer festen Variablen zugeteilt ist) nicht mit zu kopieren, bzw auch dies in der neuen Mappe zu löschen...

    Gruß
    Thomas

    ereza schrieb:

    so würde mir dieses "Hilfstabellenblatt" ungewollter weise mitkopiert werden
    Du kannst in deiner Kopierschleife abfragen, ob ws.Name <> "Hilfsblatt" ist.
    Ich mache das üblicherweise so, dass die zu kopierenden Blätter bestimmte Namensmuster ausweisen.
    Oder halt die Blätter, die nicht kopiert werden sollen.
    If Not ws.Name Like "_*" Then ws.Copy... kopiert nur Blätter, die nicht mit "_" beginnen.
    Alternativ kannst du solche Hilfsblätter auch verstecken und nur die Blätter kopieren, die sichtbar sind.
    If ws.Visible Then ws.Copy...
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    Ok!

    Lieber petaod, Lieber TommyDerWalker!


    Vielen Dank für eure Inputs!!!

    ich habe, das, was petaod vorgeschlagen hat umgesetzt und den Code von TommyDerWalker angepasst! jz funktionierts echt super!!!

    Visual Basic-Quellcode

    1. Sub CommandButton1_Click()
    2. Dim rng As Range
    3. Dim r As Long
    4. Dim wksa As Worksheet
    5. Dim wksn As Worksheet
    6. Dim wb As Workbook
    7. Dim ws As Worksheet
    8. Dim sh As Shape
    9. Set wksa = ActiveSheet
    10. Set wksn = Worksheets.Add
    11. r = wksa.Cells(Rows.Count, 1).End(xlUp).Row
    12. Set rng = wksa.Range(wksa.Cells(1, 1), wksa.Cells(r, 7))
    13. rng.Copy
    14. wksn.Cells(1, 1).PasteSpecial xlPasteAll
    15. Set wb = Workbooks.Add(xlWBATWorksheet)
    16. wb.Sheets(1).Name = "deleteMe"
    17. For Each ws In ThisWorkbook.Worksheets
    18. If ws.Name <> wksn.Name Then
    19. ws.Copy After:=wb.Sheets(wb.Sheets.Count)
    20. End If
    21. Next
    22. For Each Link In wb.LinkSources(xlLinkTypeExcelLinks)
    23. wb.BreakLink Name:=Link, Type:=xlLinkTypeExcelLinks
    24. Next
    25. For Each ws In wb.Worksheets
    26. UsedRange.Formula = UsedRange.Value
    27. For Each sh In ws.Shapes
    28. sh.Delete
    29. Next
    30. Next
    31. Application.DisplayAlerts = False
    32. wb.Sheets("deleteMe").Delete
    33. wb.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", "_" & Format(Now, "yyyy-mm-dd__hh-mm") & ".xlsx"), xlOpenXMLWorkbook
    34. Application.DisplayAlerts = True
    35. wb.Close False
    36. Tabelle15.Range(Cells(1, 1), Cells(r, 7)).ClearContents
    37. wksn.UsedRange.Copy wksa.Cells(1, 1)
    38. Application.DisplayAlerts = False
    39. wksn.Delete
    40. Application.DisplayAlerts = True
    41. End Sub


    Leute, vielen herzlichen Dank nochmals!!!

    LG Tim